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.

The Excel Double VLOOKUP Trick (Nesting VLOOKUPs)

We have already seen how to use the basic VLOOKUP function, in a previous post.

We are now going to look at a more advanced situation, where we will need to use more than one VLOOKUP function within a single formula. So if you are unfamiliar with the VLOOKUP function or its syntax, you will need to first review our previous VLOOKUP post.

So let’s get started and look at a typical scenario that requires the use of two VLOOKUPs within one formula. This situation is referred to as double VLOOKUPsor nesting VLOOKUPs within the same formula.

Examples from cell E2 in Figure -

=VLOOKUP(VLOOKUP(D2,D7:E10,2,0),G7:H10,2,0)

Vlookup function in excel

Fix your VLOOKUP in Excel

VLOOKUP stands for vertical lookup. This function works well with a standard table layout that has a code, or value, in the left column of the table. The other columns in the table can be accessed by “looking up” the code or value in the left column.

Most issues relate to the data table, especially when it is imported from other systems. The exact match type of VLOOKUP is the one usually affected. You specify an exact match by including FALSE or a zero at the end of the VLOOKUP function.

Examples from cell B2 in Figure -

=VLOOKUP(A2,D2:E5,2,0)

=VLOOKUP(A2,D2:E5,2,FALSE)