I do a lot of work in Excel and I’ve used the INDEX and MATCH functions together instead of a VLOOKUP or HLOOKUP but always stumble through that process. I wanted to write a simple guide to help me in the future.

ID	Item	Color
1	Shoes	Black
2	Socks	Brown
3	Pants	Blue
4	Shirts	Red

Pretending for a minute that is our spreadsheet, let’s look at the functions in turn.

INDEX

Here is the function syntax: INDEX(array, row_num, [column_num]). Here is how I think of it and use it: INDEX(some rectangular shaped data set in excel, row number, column number)

Using the example, =INDEX(A1:C5,2,3) would return “Black” because that’s the data in the second row and third column of the rectangular shaped data set A1:c5.

MATCH

Here is the function syntax: MATCH(lookup_value, lookup_array, [match_type]) Here is how I think of it and use it: MATCH(what are you looking for, what rectangle of data should we search?, what kind of match are you looking for?)

Using the example, =MATCH(“Socks”,B1:B5,FALSE) would return “3” because “Socks” is in the 3rd column of that array.

INDEX and MATCH together!

Those functions are pretty useless on their own…but together they are awesome. Let’s say I want to know the color of the item with an ID of “4” – using these functions I can retrieve that data!

=INDEX(the column that has the information I want, MATCH(thing I’m looking for, column that has the data I’m starting with, FALSE))

Using the example, =INDEX(C1:C5,MATCH(“Shoes”,B1:B5)) would return “Black” because that is the data point at the intersection of column C and the row that contains the data point shoes.

Question to help the process:

• What column has the information you want?
• What data are you starting with?
• What column has the data you are starting with?