Two-way lookup with INDEX and MATCH

What are the INDEX and MATCH functions?

The INDEX function is used to return a value at a given position in an array. This function can be used to retrieve individual values or entire rows and columns.

The MATCH function is used to get the relative position of an item/a lookup value in an array; a row, column, a list, or table.

Formula

Generic formula for INDEX Function:

=INDEX (array,row_num,[col_num],[area_num])

Generic formula for MATCH Function

=MATCH(lookup_value,lookup_array,[match_type])

Explanation

The INDEX is often used with the MATCH function to perform a two-way lookup where MATCH locates a value and feeds its position to INDEX.

In summary; MATCH finds the position of a value in a list while INDEX would get the value at that position.

Syntax

=INDEX(data,MATCH(val_1,rows,1),MATCH(val_2,columns,1))

Example

How to use the INDEX and MATCH functions to perform a Two-way lookup

In this example, our spreadsheet would be containing two tables; one is a multiplication table and the other is a table where we are supposed to calculate the product of the two numbers given on the table.

The following steps should be taken;

  1. Open the existing spreadsheet

Figure 1. Sample spreadsheet for Two way lookup with INDEX and MATCH

  1. Click on the cell where you would like the results of your two-way lookup to be displayed.

Figure 2. Click on the cell

  1. Insert the following formula into the cell you have clicked to get the product of 9 and 10 from the table; =INDEX(C6:G10,MATCH(J6,B6:B10,1),MATCH(J7,C5:G5,1))

Figure 3. Insert the formula into the cell

  1. Press Enter, the value retrieved and displayed should be 90.

Figure 4. Press ENTER to get the retrieved value

  1. Insert the following formula to get the product of 7 and 8 from the table; =INDEX(C6:G10,MATCH(K6,B6:B10,1),MATCH(K7,C5:G5,1)) then Press Enter.

The image below displays our final results.

Figure 5. Final Results Two Way lookup using INDEX and MATCH”

Notes

How the formula, “=INDEX(C6:G10,MATCH(J6,B6:B10,1),MATCH(J7,C5:G5,1))” worked:

  • MATCH(J6,B6:B10,1) picked the value in cell J6 (“9”), located its position (5) between row B6:B10 and fed this position to INDEX.
  • MATCH(J7,C5:G5,1) picked the value in cell J7 (“10”), located its position (4) between column C5:G5 and fed this position to INDEX.
  • INDEX took these rows and column positions of our array (row 5, column 4) and retrieved the value using those position to search through array C6:G10 (which is the multiplication table).
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar