How to perform a Two-Column Lookup

How to perform a two-column lookup in Excel. See the example below. We want to look up the Price of Lenovo i3, not Lenovo i5, not Lenovo i7.

➤ To join strings, use the & operator.

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

➤ 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:A10&B2:B10 is stored in Excel's memory, not in a range. The array constant looks as follows:

{"Lenovoi3","Lenovoi5","Lenovoi7","HPi3","HPi5","HPi7","Delli3","Delli5","Delli7"}

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

➤  Use this result and the INDEX function to return the 1st value in the range C2:C10.