Lesson 11: INDEX and MATCH: look up to the left

Practice exercises for accountants, checked instantly in your browser.

VLOOKUP can only return columns to the right of the lookup column. Accounts payable files often have the ID first and the name second, so finding an ID from a name needs INDEX and MATCH.

MATCH(value, column, 0) returns the position of the value in a column, and the 0 means exact match. INDEX(column, position) returns the item at that position. Together, =INDEX($A$2:$A$6,MATCH(F2,$B$2:$B$6,0)) finds the name in column B and returns the ID from column A in the same row.

Worked example: =MATCH("Kestrel IT Services",B2:B6,0)
Returns 3: it is the third name in the list.

Exercises

Exercise 1

Kinsley Property Group's vendor master is in A1:D6. In G2, return the Vendor ID for the name in F2 and fill to G4.

Exercise 2

In H2, return the payment Terms for the vendor named in F2, using INDEX and MATCH. Fill to H4.