Use an INDEX/MATCH (Lookup either way)

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area
above and selecting ‘Refresh’. If you are still unable to view the file
properly, or it contains unsupported features, click here to download a copy.

Instead of using a VLOOKUP, you can use the INDEX and MATCH functions to perform a lookup. This is a more flexible approach as you can lookup both left-to-right and right-to-left: look up a value in one column and return the corresponding value in the same row of any other column

In the Topic Example Workbook, say we want to use the MATCH and INDEX functions to find the quantity of products sold by James (i.e. do a lookup Left to Right – lookup the value “James” in Column A and return the corresponding value in the same row of Column C)

We will do this by:

• Using the MATCH Function to return the row number in which “James” occurs in Column A
• Using this as part of an INDEX Function –looking across Column C and return the value in the row number we found using MATCH.

The MATCH function looks for a value in a column and returns the row number on which it occurs. (Or alternatively it can look across a row for a value and return the column number in which it occurs).

In the Topic Example Workbook we can use a MATCH function to determine which row in Column A the value “James” occurs on (we can see that this is row 3, so this is what we expect the MATCH function to return.)

The syntax of the MATCH Function is =MATCH(“Lookup_Value”,”Lookup_Array”,”Match_Type”)
The table below describes each element of the syntax and its applicable value in the example workbook.

Therefore our completed MATCH FUNCTION is =MATCH(“James”, A:A,0). If you type this into cell F2 above you will obtain a value of 3 as expected, indicating that within Column A, the value “James” occurs on Row 3.

The Index function works like a map grid reference: given a range of cells (e.g. a column/set of columns or a row/set of rows), this returns the value in a specified row and column.

For example in the Topic Example Workbook we could use the INDEX function across columns A:C and return the value in the 3rd row and 2nd column of that range. Our Index formula would be =INDEX(A:C,3,2) and this would return the result “DVD” which is the value in cell B3 (i.e. the value in the second column and third row)

The syntax of the INDEX Function is =INDEX(“array”,”row_num”,”Column_num”). In this case, we want the array to be Column C, the row number to be the result of our MATCH Function and the column number to be 1 (we’re only looking across one column – Column C- so it has to be 1!)

Just focusing on the INDEX function, our completed formula is now =INDEX(C:C,3,1) – look in Column C and return the value on the 3rd row. If you type this into cell F40 in the above workbook it returns a value of 200, which is the quantity which James sold.

Of course we want the row to vary according to what we’re looking up in Column A, so we now need to combine the MATCH and INDEX functions, replacing the 3 in our INDEX formula with the MATCH function which we wrote above.

The combined formula is =INDEX(C:C,MATCH(“James”,A:A,0),1) – Look in Column C, return the value in the row where we find “James” in Column A and the first (only) column. If you type this into the Excel workbook in cell F42 it also returns 200.

The INDEX function can also be used to return a entire row or column as its result – putting 0 as the row parameter returns the whole of the column which is referenced in the INDEX function. E.g. =SUM(INDEX(A:C,0,3)) is the same as =SUM(C:C) which returns the sum of Column C. Likewise =SUM(INDEX(A:C,1,0)) is the same as =SUM(1:1) which returns the sum of values in Row 1. In this way (particularly when combined , the INDEX can be used to create a dynamic range reference much like using the OFFSET function.

In the Topic Example Workbook, use the INDEX/MATCH Functions to return the quantity sold by each Salesperson in cells F49 to F52.

Then below this, try using the INDEX/MATCH functions to do a lookup the other way – given quantities in Column C work out which Salesperson sold that quantity

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

The following are some things to think about when using an INDEX/MATCH Formula

1. When should I use an INDEX/MATCH rather than a VLOOKUP?
• Always use INDEX/MATCH rather than VLOOKUP – it’s more flexible, not only because you can look up either way but it’s also less likely to break.
• To see this, see cells F64 and F65 in the sample workbook – one is a VLOOKUP and the other an equivalent INDEX/MATCH function.
• Now try inserting a column in between columns A and B in the example workbook. The VLOOKUP formula now returns the wrong result (it shows DVD instead of 200) though the INDEX/MATCH function still works
• This is because we hard-coded the value of 3 in the VLOOKUP formula – indicating that the column we want to look up in (Column C) is 3 columns away from A. Once we insert a column our required lookup column is now 4 columns away but our VLOOKUP formula hasn’t flexed to reflect this.
• We could get around this by using the MATCH function within the VLOOKUP – rather than hard-coding the value of 3 we use =MATCH(“Quantity Sold”,1:1,0) – i.e. look in row 1 for the value ‘Quantity Sold’ and return the column number. But then now that we’re using the MATCH function anyway, we might as well just combine it with the INDEX function as per above

2. What happens when there is more than one occurrence of the lookup value in the range (e.g. if there were two occurrences of “James” in Column A)

• INDEX/MATCH (or VLOOKUP) will only return the first Instance
• To tell if there is more than one occurrence – use a COUNTIF function
• To deal with multiple occurrences you’ll need to make the value unique by combining the values in multiple columns– see LOOKUP against multiple columns
• Using an Array Formula, It is also possible to do a lookup and return multiple values (Advanced)

3. How can I make the VLOOKUP Formula easier to understand?

• Use named ranges instead to refer to cells/columns

YouTube Video – Do a Two Way Lookup in Excel – Match() Index() Functions