

The VLOOKUP formula in its base version only works from left to right. The search column must be located on the left-hand side of the return column. What if your data doesn’t have such structure? There is a way for using the VLOOKUP to the left but it requires an array form of the formula. It’s often worth considering alternative formulas though. Here is everything you should know.
Contents
The VLOOKUP formula can be used for returning a value on the left of a search value. But you have to use it in the array form. Because using the VLOOKUP formula as an array formula comes with several disadvantages, please consider two different approaches if your search column is on the right-hand side of the return column.
Structure of the VLOOKUP formula to the left.
Like in the previous article—multi-condition lookups—the trick is to create a virtual table (an array) using the CHOOSE formula within the VLOOKUP formula. In this virtual table, you change the structure of the input data so that the search value is located on the left-hand side of the return value.
The structure of the VLOOKUP formula is shown in image on the right-hand side.
As usual for array formulas, press Ctrl + Shift + Enter after you finished typing. The curly brackets are added automatically that way.
Example for the VLOOKUP formula to the left.
Say you have sales data in the cell range B4 to C7 like shown in the screenshot on the right-hand side. The revenue is given in the cell range B4 to B7 and the category in C4 to C7. The task is to return the revenue depending on the selection of the category.
That means you have to following input values:
Assembling all these inputs you will have the following formula for a VLOOKUP to the left.
{=VLOOKUP(G3,CHOOSE({1,2},C4:C7,B4:B7),2,FALSE)}
Again, don’t forget to press Ctrl + Shift + Enter instead of just Enter on the keyboard after you finish to type the formula.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
Actually, I’ve only added this section for the sake of completeness. In most cases (well, I can’t think of a case you should use the HLOOKUP formula bottom-up) I’d recommend using INDEX/MATCH in a horizontal way instead.
HLOOKUP works top-down. But like the VLOOKUP formula before it can be used to work bottom-up. The structure of the HLOOKUP formula in the array form is very similar to VLOOKUP in the previous section. There is one difference, though: You have to add the TRANSPOSE formula within and around the CHOOSE formula in order to convert the horizontal cell ranges to vertical cell ranges. Again, there are usually easier options for achieving an HLOOKUP from bottom to the top.
Structure of the HLOOKUP formula from bottom to the top.
The structure of the HLOOKUP formula is shown in the screenshot on the right-hand side. The numbered arguments in this screenshot are the same like in the VLOOKUP formula to the left above.
An example for the bottom-up HLOOKUP is shown in image below. Like before you want to return the revenue from the cell range C3 to F3 based on the selection of the category in cell range C4 to F4.
Example for a bottom-up HLOOKUP formula.
Assembling the argument in Figure 102 leads to the following formula.
{=HLOOKUP(D7,TRANSPOSE(CHOOSE({1,2},TRANSPOSE(C4:F4),TRANSPOSE(C3:F3))),2,FALSE)}
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
Please note:
Please feel free to download an Excel workbook with all the examples from this article. Just click this link and the download starts immediately.
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.