MVP #46: Excel VLOOKUP, HLOOKUP and the combination of INDEX & MATCH part 3

This is the final part of the series on VLOOKUP, HLOOKUP and the combination of INDEX & MATCH. You can read the first part here and the second part here.

In the previous parts, I broke down in a very easy to understand way how to use VLOOKUP and HLOOKUP, and also when to use them. In this post, I am going to show you the amazing power of INDEX & MATCH and how easy it is to use. You can download the practice file here.

As usual, we need a situation. A business need. And we will continue with the HR Employee table in the practice file we've been using. You are the company's HR Manager. You woke up this morning to see an email from the CEO with a list of phone numbers that he says are for employees he didn't save their contact details properly on his phone. So he wants you to provide him their details from the employee database you manage.

The first thing you did when you got work today was to create a table to house the details you want to send him. The table looks like the one below:

And below is a screenshot of the Employee database you manage.

You are not ready to start typing the details you need to send the CEO or do multiple copy and paste. You want to use a formula you can input in the first record row and drag down to do for the other records. A formula that will look for the phone number in the Employee table and give you the corresponding Employee Number, First Name, Last Name and Email.

You might be tempted to use VLOOKUP since this looks so much like what we used VLOOKUP for in the first part. And I will say you should give it a try. Seeing things for yourself is better than just having someone tell you that VLOOKUP won't work in this case. And the reason is that VLOOKUP only picks records that are to the right of the lookup value. In this case, the Employee Number, First Name and Last Name that we want to pick are to the left of the Phone Number that is our lookup value. Hence, VLOOKUP won't work.

You will have to use INDEX & MATCH. And they are very easy to understand and use.

INDEX works like a cabinet with many rows and columns of drawers. Each drawer holds one item or record. And to reach a specific drawer you simply provide the row position and column position. And this is very similar to having an Excel table with rows and columns of data. Using INDEX on such a table, you can pick the record in a particular row and column. For example, row 1 column 1 for the first record in the table.

So in this case, you will be using INDEX to pick the records the CEO has requested for. You already know that in the Employee data table, Employee Number is in column 1, First Name is in Column 2, Last Name is in Column 3 and Email is in Column 5. What is left is the row number. Unfortunately, the row number will depend on where the phone number you are looking for is in the Employee data table; the row it is on. And this where you will need MATCH.

MATCH is a very simply Excel formula that tells you the position of a record. It is what will tell you the row the phone number we need the details of is on the Employee data table. And that is what we will give to INDEX to provide us with the corresponding Employee Number, First Name, Last Name and Email.

Now you know the logic of what to do. It's time you began inputting the formulas. Your CEO is not a patient man.

 And here is the formula: (It looks long because the tables are in different sheets)

=INDEX('HR Database'!$A$1:$I$201,MATCH('Urgent Request'!$B$3,'HR Database'!$D$1:$D$201,0),1)

And here is the explanation of how to input it and the logic flow.

Remember I said INDEX gives you what is in a specific row and column of a table. So INDEX has the following formula syntax:
=INDEX(table, row number, column number)

In this case the table is 'HR Database'!$A$1:$I$201
The row number is gotten for us by MATCH('Urgent Request'!$B$3,'HR Database'!$D$1:$D$201,0)
The column number is 1 (for Employee Number)

Now you understand the INDEX logic.

Next is MATCH.
MATCH gets you the position of a record. And its syntax is
=MATCH(record, record_field, exact_or_not_exact)
And in this case, the record is the phone number you know, the record field is the phone number column in Employee Data table and you tell Excel to give us exact match by typing 0.

And that's what you did with MATCH('Urgent Request'!$B$3,'HR Database'!$D$1:$D$201,0)
You told Excel to look for the record 'Urgent Request'!$B$3
in the record field 'HR Database'!$D$1:$D$201
and give an exact match, 0

This will make MATCH return the row position the phone number is on. Which INDEX will use to get us the Employee Number (Column 1). And we simply do the same to get First Name, Last Name and Email, just by changing the Column number INDEX should pick from.

Now try doing for the other records.  And a tip: change Urgent Request'!$B$3 to Urgent Request'!$B3 to be able to drag the formula down and have it work for the other records. Don't forget to download the practice file here.

In a subsequent post I will explain the difference between $B$3, B3, $B3 and B$3. In that post you'll learn to build a Multiplications Table simply by dragging one formula. It's the one activity that makes the brightest participants in my training classes go on a long meditation.

Till then, keep Excel-ing!