MVP #41: Excel VLOOKUP, HLOOKUP and the combination of INDEX & MATCH part 1

A lot of times in Excel, we have a comprehensive sheet that contains data we would like to pick for another report or analysis in a completely different sheet or file. Doing copy and paste could be very tedious. 

Excel has built-in functions that allow us pick data from same sheet, another sheet or another file for specific entries/records. These functions are VLOOKUP, HLOOKUP and the combination of INDEX & MATCH. 

To explain we are going to use a fictitious HR table. A table that consists of everyone in an unknown company. This will make up the comprehensive sheet that has all the data we could ever need. Here is that practice file, VLOOKUP - HLOOKUP - INDEX - MATCH file.

This is what the HR database (table) look like: 

There are 200 employees with their records.

VLOOKUP
Let's say there is a yearly competition the company does, called -- The Most Helpful Employee of the Year. So in December everyone votes for who they believe the most helpful employee is for that year. 

It's almost the end of December and the voting has been done. Lekan Tijani with employee id 10000173 has been voted the most helpful employee of the year. But the form doesn't contain his email, phone number and other details we will like to know about him. So we need to look him up in the HR comprehensive table and pick his other details from there.

There are 2 ways we can do this. 

Number 1: we can use the Find tool in Excel (CTRL + F) to find the employee id 10000173 (which is for Lekan Tijani) and copy out the other details we need from his record. The downside to using this method is that if we have a special sheet that holds the records of all the winners since the company began this competition, then the day Lekan changes his phone number and notifies the HR department you will have to make the changes in both the HR comprehensive table and this winners table. The second drawback in using this method is that if we expand the winning categories to include not just most helpful employee of the year but adds 10 other categories (most handsome employee of the year, most beautiful employee of the year, most aggressive employee of the year, most hardworking employee of the year ...) you will have to do find, copy and paste very many times. But there's a better way, the number 2 way.

The number 2 way is to use VLOOKUP. You can use VLOOKUP to search for the row with the employee id 10000173 (Lekan Tijani's row) and pick the other data from that row that you want (phone number, email...). This method will overcome the 2 drawbacks I highlighted for the number 1 method. When Lekan changes his phone number you only need to update the HR comprehensive table, the winners table will update automatically. And doing for other categories would be as easy as dragging the formula.

How do we use VLOOKUP? Very easy. It easier than most people think or expect. 

We have a winners table we need to populate with extra details from the HR comprehensive table.

We need to use VLOOKUP to pick the phone from the HR comprehensive table (in another sheet). The VLOOKUP formula syntax is:

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

and in English it is:

=VLOOKUP(the_record_you_want, the_table_with_everything, the_field_you_want, what_Excel_should_do_if_it_doesn't_find_the_record)

And here's how we will use it in this case.

We want the record for employee id 10000173. Hence the need to type =VLOOKUP(B2,

Next is to specify/select the table that has everything (the HR comprehensive table).

By selecting the HR comprehensive table Excel puts the address for us (very helpful when the table is in another sheet or file entirely). We now have =VLOOKUP(B2,'HR Database'!A1:E201,

Next is to tell VLOOKUP what field we need.

We count from the first field to determine the number/position of the field we want.

The phone number is in the 4th field. So our VLOOKUP formula is almost done, =VLOOKUP(B2,'HR Database'!A1:E201,4

Final step is to tell Excel what to do when it doesn't find what we are looking for. There are two possible things to enter:

1. FALSE. This will tell Excel to display an error when it doesn't find the record we are looking for. Maybe someone has deleted the row for Lekan Tijani in the HR comprehensive table or we typed a wrong employee id.
2. TRUE. This will allow Excel to give us what it considers as the closest record to what we are interested in that we couldn't find. I generally avoid using this option.

In our case we would prefer Excel give us no result if it could not find Lekan Tijani's record in the HR comprehensive table instead of giving us someone else's record.

So we do =VLOOKUP(B2,'HR Database'!A1:E201,4,FALSE) 

Not forgetting to close the bracket, to signal the end of the formula.

And that is all. We should see Lekan's phone number now.

Now you understand how VLOOKUP works and have seen an almost real-life case where we needed to use it.

Try doing for the email. (Tip: the email record is in the 5th field)

Again, you can download the practice file here: VLOOKUP - HLOOKUP - INDEX - MATCH file.


Expect the 2nd part, which is on HLOOKUP.