Looking for VLOOKUP Trick

[shdawson]

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S

 

[Richard Schollar]

You mentioned VLOOKUP tricks - demo'd by Barry Houdini the other day

=VLOOKUP(A2,CHOOSE({1,2},'Tasks Report'!X$2:X$10000,'Tasks Report'!L$2:L$10000),2,0)

That's awesome! That Barry is a clever fellow

 

[Matty]

I notice that you have omitted the last part of MATCH in your formula, which denotes the match-type to be used (1 is the default if omitted).

If you want an exact match (which most of the time is what is required), use 0 as the match_type, like:

=INDEX('Tasks Report'!L$2:L$10000,MATCH(A2,'Tasks Report'!X$2:X$10000,0))

But I suspect this may not fix your problem. Have you run it through the formula evaluator to assess where it's failing?

Matty

 

[shdawson]

Your code works better.

Now, I take it...if nothing is found, the cell gets populated with a 2 (though not clear what this is)? Please advise.


Thanks,
S

 

[Matty]

=VLOOKUP(A2,CHOOSE({1,2},'Tasks Report'!X$2:X$10000,'Tasks Report'!L$2:L$10000),2,0)

That's neat.

DonkeyOte - do you have a link to the Thread where Mr Houdini originally used this magic, please?

Thanks,

Matty

 

[DonkeyOte]

DonkeyOte - do you have a link to the Thread where Mr Houdini originally used this magic, please?

It was on a different forum so I'd have to check with him first that he's ok with that... essentially it was demo'd as part of a general discussion regards the merits/uses of CHOOSE.

Now, I take it...if nothing is found, the cell gets populated with a 2 (though not clear what this is)? Please advise

If you're referring to the VLOOKUP alternative - it works just the same as any normal VLOOKUP - ie will return #N/A if criteria not found.

 

[shdawson]

Works for me.

Thank you, gentleman. Have a great day.


Kindly,
S

 

[Matty]

It was on a different forum so I'd have to check with him first that he's ok with that... essentially it was demo'd as part of a general discussion regards the merits/uses of CHOOSE.

OK, no worries.

Cheers,

Matty

 

[DonkeyOte]

Matty, below is the link (having ok'd with BH first)

http://www.excelforum.com/excel-general/753261-when-would-someone-use-the-choose-function.html

 

[Matty]

Thanks for posting the link, DonkeyOte. And thanks to Barry Houdini for sharing this clever trick.

Matty

 

[Marcelo Branco]

WOW... a left VLOOKUP !!!

Thanks for posting the link, DonkeyOte. And thanks to Barry Houdini for sharing this clever trick.

Matty

Same here!!!

M.