Skip to main content

Left lookup with VLOOKUP

Author: Amanda Li Last Modified: 2021-11-22

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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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.
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