Note: The other languages of the website are Google-translated. Back to English
English English

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.

left lookup with vlookup 1

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.

left lookup with vlookup 2

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:
    left lookup with vlookup 3
  • 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

Excel VLOOKUP function

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.

Excel CHOOSE function

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations