Richard Schollar: VLOOKUP Left!

VLOOKUP WEEK

Richard Schollar provides this guest post. Richard is an Excel MVP from the U.K.  When I saw the title of Richard’s post, I figured he was going to use INDEX/MATCH, but Richard really uses VLOOKUP!

How do you use a VLOOKUP if your table data holds the lookup value in a column to the right of the data column you want to return?

We’ve all had this problem – you want to use VLOOKUP but your data is ‘round the wrong way’:

You have the ID, you want to return the Description.  A normal VLOOKUP won’t work as you can’t use a negative column:

One alternative is to use INDEX/MATCH e.g.:

But wouldn’t it be cool to be able to use VLOOKUP?  But we can’t though, can we?  Sure we can, if we make use of the CHOOSE function:

That formula in G2 is:

That formula is =VLOOKUP(F2,CHOOSE({1,2},$C$2:$C$7,$A$2:$A$7),2,False)

How it…

Ver la entrada original 52 palabras más

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s