Excel CHarts

 Problem #3:   A member of a leading online Excel forum once quipped that he hated the fact that VLOOKUP() is “Zoolander challenged”.  In the movie Zoolander, Ben Stiller plays male model Derek Zoolander, who is not an “ambiturner”.  Derek can only turn right on the catwalk; he can’t turn left.  Likewise VLOOKUP() can only fetch back data from columns located to the right of the key, it cannot return from columns to the left.   Let’s say we need to return the type of flower given the one-letter key from the table below (we’ll assume the table is located in A1:B6).

Flowers

Key

Roses

R

Carnations

4

Lilies

L

Zinnias

9

Tulips

T

Solution #3:   The way to return data from the left is to understand two additional functions, INDEX() and MATCH().  The MATCH() function also looks something up for us.  But instead of returning what’s in the cell, it tells us the position in the list where it matches. The formula =MATCH(“L”,B1:B6,0) will return 4.

The INDEX function returns the value of the contents of a range based on position.  So the formula =INDEX(A1:A6, 4) will return “Lilies”. 

 So if we set that MATCH() inside the INDEX()

  • =INDEX(A1:A6,MATCH(“L”,B1:B6,0))

 We now have a formula that can look left and return “Lilies” given the input “L”.

Unfortunately, MATCH() suffers the same inability to convert between numbers and text as VLOOKUP(), so if we have a mix of General and Text, we need to use the same Text to Columns or in-formula coercions as described in Solution #2. 

Bonus – Once we become adept at using the team of INDEX() and MATCH() together, we can also employ them in an alternate solution to our composite key problem.  Instead of inserting a helper column, we can have Excel do the concatenation inside the formula by entering the formula as an array formula.  Notice the formula for cell G4 that is shown in the formula bar below.

We type the formula in without the braces – the “{“ and “}” – but instead of confirming the formula with a simple ENTER keystroke, we confirm it with CTRL+SHIFT+ENTER.   To gain a better understanding of how the array formula actually works we can use the Evaluate Formula tool on the Formula tab in Excel 2007 & 2010, or from the Tools, Formula Auditing, Evaluate Formula… path in Excel 2003.  I leave it to the reader to do this exploration.

In summary we now have solutions to three of our most frequent challenges in using the VLOOKUP() function:

  • We can now look up based on more than one column
  • We can troubleshoot cells where we’re getting an #N/A though the key is valid
  • We are now “ambiturners” – we can fetch data from columns to the left of our key column.

Go to Main Page/Problem #1

Go to Problem #2:

Also read
Learn to use VLOOKUP() in Excel
Troubleshoot VLOOKUP() Formula – The Common Mistakes

Comments

  1. Luigi

    Hello!
    I’m a passioner as you of vba Excel.
    In your point of view, against the recent development of internet language programming as .Net, will vba for Excel survive?
    Or if will gradually die?
    Thanks for your response.

Leave a Reply

Your email address will not be published. Required fields are marked *

two × three =

This site uses Akismet to reduce spam. Learn how your comment data is processed.