The “Zoolander problem”
Like Ben Stiller’s male model character in Zoolander, the vlookup has a problem: it can’t turn left. Vlookups only work when the column you are looking up is to the right of your lookup value.
You will need to get creative if the column is to the left of your lookup value. There are formulas other than vlookup that could achieve this, but they are beyond this book’s scope.
My solution: force the column data to the right of the vlookup. You can do this by cutting and pasting or by adding a column to the right of your lookup value that is a cell reference to your source data.
In our example below, the sales and profit figures are stored to the left of the store number. To do a vlookup using the store number as our lookup value, I made cell references in Columns E and F back to Columns A and B.
It’s Not Pretty, But…
Excel purists will scoff at this solution. Yes, there are more elegant ways around this problem. As often, here I defer to Chandoo.
My goal in this series is to make you a competent data analyst as quickly as possible. You will learn the more advanced Excel tricks as you progress. For now it’s more important to learn the 20% of Excel tricks that are used 80% of the time and to frame your data analysis skills like a seasoned analyst.
Leave a Reply