Two-column Lookup in Excel

This example teaches you how to perform a two-column lookup in Excel. See the example below. We want to look up the salary of James Clark, not James Smith, not James Anderson.

1. To join strings, use the & operator.

2. The MATCH function returns the position of a value in a given range. Insert the MATCH function shown below.

3. Finish by pressing CTRL + SHIFT + ENTER.

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.

Explanation: The range (array constant) A2:A8&B2:B8 is stored in Excel’s memory, not in a range. The array constant looks as follows:

{“JamesSmith”;”JamesAnderson”;”JamesClark”;”JohnLewis”;”JohnWalker”;”MarkReed”;”RichardLopez”}

This array constant is used as an argument for the MATCH function, giving a result of 3 (JamesClark found at position 3).

4. Use this result and the INDEX function to return the 3rd value in the range C2:C8.