Left lookup with VLOOKUP
In this tutorial, we will discuss about how to use VLOOKUP to find out the information listed in the left columns in Excel about a given item which is at the right side. We may know that the VLOOKUP function cannot look to its left, so in order to accomplish the task, we will have to use the help of the CHOOSE function.
How to perform a left lookup with VLOOKUP?
To fill in the information about the product 30001 which is listed in the rightmost column of the data as shown in the screenshot above, you can use the CHOOSE function to reorder the table – “move” the rightmost column with the lookup value to the leftmost position. Then you can use a normal VLOOKUP formula to retrieve the value you want.
Generic syntax
=VLOOKUP(lookup_value,CHOOSE({1,2,3,…,N},rangeN,range1,range2,range3,…),column_num,FALSE)
- lookup_value: The value VLOOKUP used to locate the position of the corresponding information. Here refers to the given product ID.
- Range1, range2, range3, rangeN: The columns of cells where the lookup_value and other information are listed.
- column_num: The number to indicate which column you want to retrieve data from. Note that since the rightmost column is now moved to the rightmost position because of the CHOOSE function, to present the 1st column (color column), you should type the column_num as 2; to present the 2nd column (size column), you should type the column_num as 3……
- range_lookup FALSE: Forces VLOOKUP to only find the exact match.
To fill in the information about the product 30001, please copy or enter the formulas below in the corresponding cells, and press Enter to get the results:
Color (Cell H6)
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,FALSE)
Size (Cell H7)
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),3,FALSE)
Price (Cell H8)
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),4,FALSE)
Note: Instead of typing the product ID 30001 in the formulas, we used the cell reference $H$4 (we add dollar signs to the reference to make it absolute) since we want the formulas to be dynamic. In this way, we can easily get the information about other products by only changing the product ID in the cell H4.
Explanation of the formula
Here we use the formula below as an example:
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,FALSE)
- CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9): The CHOOSE function reorders the columns by moving the column E to the first, or we say, the leftmost position in an array like this:
{30001,"White","Large",20;30002,"Black","Large",21;30003,"Blue","Medium",19;30004,"Red","Medium",18;30005,"Yellow","Small",20}.
In table form, the array would be like this:
- VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,FALSE) = VLOOKUP($H$4,{30001,"White","Large",20;30002,"Black","Large",21;30003,"Blue","Medium",19;30004,"Red","Medium",18;30005,"Yellow","Small",20},2,FALSE): With 30001, the lookup value in cell H4, the VLOOKUP function will locate the row of its exact match in the new table created by CHOOSE, and then return the result at the 2nd column, which is White.
Related functions
The Excel VLOOKUP function searches for a value by matching on the first column of a table and returns the corresponding value from a certain column in the same row.
The CHOOSE function returns a value from the list of value argument based on the given index number. For example, CHOOSE(3,”Apple”,”Peach”,”Orange”) returns Orange, the index number is 3, and Orange is the third value after index number in the function.
Related Formulas
Left lookup with INDEX and MATCH
To find out information listed in the left columns in an Excel spreadsheet about a given item which is at the right side, you can use the INDEX and MATCH functions. The combination of the two functions possesses an advantage of looking up values in any column over another Excel’s powerful lookup function, the VLOOKUP.
Lookup values from another worksheet or workbook
If you know how to use VLOOKUP function to search for values in a worksheet, vlookup values from another worksheet or workbook won’t be a problem for you.
Lookup closest match value with multiple criteria
In some cases, you may need to lookup the closest or approximate match value based on more than one criteria. With the combination of INDEX, MATCH and IF functions, you can quickly get it done in Excel.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
