Excel Left Lookup Formula Using VLOOKUP Explained

How many times did you needed retrieving values from columns on the left in your excel table ? And how many of those times did you ended up moving columns so that you could use VLOOKUP function ?
Well, that's over... from today onwards you will no longer need to adjust any columns in your excel file. I'll show you how to use VLOOKUP function to retrieve values from columns on the left.

Lookup values on the LEFT

Let's imagine that you have the following table with all sales made by your staff during the last 6 months.

Now, If I wanted to retrieve how many pens have been sold I could easily use VLOOKUP as follows:
=VLOOKUP("Pen",$D$1:$E$1,2,FALSE) and it would retrieve 27 (Cell E5)

But what if I'd need to know in which region have the Pens been sold ? Then I'd have a problem using Vlookup as I'm able to search to the left of Column D!

Well, here's a work arround:
Combine VLOOKUP with CHOOSE function.

Question: In which region have the Pens been sold ?
Fórmula: =VLOOKUP("Pen",CHOOSE({1,2},$D$2:$D$12,$B$2:$B$12),2,FALSE)
Result: Central

Formula explained:
The only part of this formula that should be new for you is the one in bold.

=VLOOKUP("Pen",CHOOSE({1,2},$D$2:$D$12,$B$2:$B$12),2,FALSE)

CHOOSE Function - chooses a  value or action to perform from a list of values, based on an index number.

The job of the CHOOSE function in the formula is to trick VLOOKUP into believing that column 4 is actually column 1. As a result, the Item name can be used as the lookup value to find the name of the part supplied by each company.