VLOOKUP and INDEX/MATCH

Beginners to Excel are almost universally aware of the basic mathematical functions of Excel, such as using SUM() to add or PRODUCT() to multiply. But I am often amazed at how few beginners are familiar with the VLOOKUP() function. This incredibly useful tool, and it’s counterpart the INDEX/MATCH functions, are the subject of the first lesson.

VLOOKUP - MOVING FROM LEFT TO RIGHT

VLOOKUP is useful when you have a list and you're trying to extract a particular piece of information from it. To keep it light, let's say your list contains a series of fast food burgers with some basic nutritional facts:

	A	B	C	D	E	F
1	Restaurant Name	Burger Name	Total Fat (g)	Calories	Cholesterol (mg)	Sodium (mg)
2	McDonald's	Big Mac	23	467	70	908
3	Burger King	Whopper	37	650	60	910
4	Five Guys	Hamburger	43	700	125	430
5	Shake Shack	Single Shackburger	30	490	101	895

This is, of course, a very simple list but if we wanted to know the total fat content of a Big Mac at McDonald's, we could use the VLOOKUP function to achieve this:

	A	B	C	D	E	F
1	Restaurant Name	Burger Name	Total Fat (g)	Calories	Cholesterol (mg)	Sodium (mg)
2	McDonald's	Big Mac	23	467	70	908
3	Burger King	Whopper	37	650	60	910
4	Five Guys	Hamburger	43	700	125	430
5	Shake Shack	Single Shackburger	30	490	101	895
6
7	Value	Formula
8	Big Mac	N/A
9	23	=VLOOKUP(A8,B2:F5,2,FALSE)

Let's talk about how the VLOOKUP function works.

=VLOOKUP(A8,B2:F5,2,FALSE)

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

The lookup_value is the name of the burger whose total fat you're interested in retrieving.

The table_array is the range of cells that contains the list of burgers in the leftmost column and must at least contain the column with the total fat values.

The col_index_num is the number of columns over from your burger column that contains your total fat, where the burger column is the first.

The range_lookup (the brackets mean it's optional and you can leave it blank> is either true or false (if left blank, it defaults to true). Use false if you want only exact matches. Use true if you're willing to accept approximate matches (I have literally never used true).

If you want to know the amount of sodium for the Shake Shack's Single Shackburger, change the col_index_num to 5, where the sodium data is. Note that although the sodium data is in column F, it is only the fifth column in the stated range of B2:F5.

	A	B
7	Value	Formula
8	Single Shackburger	N/A
9	895	=VLOOKUP(A7,B2:F5,5,FALSE)

If you had set your range of cells, the total_array to stop at column C (say, from B2:C5 rather than B2:F5), you would get an error (#REF!) if you tried to retrieve a value outside of the stated range, such as from column D.

INDEX/MATCH - MOVING FROM RIGHT TO LEFT

VLOOKUP only works when you have something in the leftmost column (such as a burger) and you want to retrieve information from a column to the right of it. If you wanted to know which restaurant sells the Big Mac, you would not be able to use VLOOKUP because the restaurant name is in a column to the left of the burger name. Unfortunately, you cannot be clever and use a negative number in the VLOOKUP function (you’ll get a #VALUE! error if you try that one).

There are two workarounds: an ugly one and a clean one. The ugly option is to simply move the restaurant name column to the right of the burger name column. That’s an ugly fix because you shouldn’t have to change the structure of the table just to satisfy your VLOOKUP function.

The clean option is found in the creative use of the combination of two functions: INDEX() and MATCH().

	A	B	C	D	E	F
1	Restaurant Name	Burger Name	Total Fat (g)	Calories	Cholesterol (mg)	Sodium (mg)
2	McDonald's	Big Mac	23	467	70	908
3	Burger King	Whopper	37	650	60	910
4	Five Guys	Hamburger	43	700	125	430
5	Shake Shack	Single Shackburger	30	490	101	895
6
7	Value	Formula
8	Whopper	N/A
9	#VALUE	=VLOOKUP(A8,B2:C5,-1,FALSE)
10	Burger King	=INDEX(A2:A5,MATCH(A8,B2:B5,0))

The INDEX function has three parts, although only two are required:

=INDEX(array,row_num,[column_num])

The array, for our purpose, is just a single column of data that contains the restaurant names you want to retrieve.

The row_num contains the MATCH() function.

The [column_num] can be ignored and left blank as it is optional

The MATCH() function also has three parts, although the third is always the same value

=MATCH(lookup_value,lookup_array,[match_type])

The lookup_value, like in the VLOOKUP function, is the name of the burger whose restaurant you want to know.

The lookup_array is simply the single column of data that contains the burgers.

The [match_type] should equal zero because zero means that you want an exact match. It’s similar to the range_lookup value within the VLOOKUP function.

But it's the combination of the two that is so powerful. The MATCH function returns the row number of your reference item (in this case, the burger name) and the INDEX function returns the value in a different column (the restaurant name) the same number of rows down.

If you haven't figured it out yet, you could technically get away with never using the VLOOKUP function as the INDEX/MATCH combination is more versatile. But the VLOOKUP function is easier to write so I still use it when moving "left to right".