PDA

View Full Version : Left Vlookup



Anne Troy
2012-01-31, 07:56 PM
With all the articles online, you would think I'd be able to make it work. I am the queen of vlookup and the slave to index/match.
Here is the problem:

In a worksheet called ScreenCats, I want to look up the value in A2 ("Lunch Specials") and find it in column B on a worksheet called MenuData, then return the value one cell to the left of it
I only need to find it once. I cannot workout the appropriate index/match configuration to save my worthless life.

I have attached a sample. If someone could put the formula in there for me or give me a literal formula here (meaning one I can copy/paste), I would be eternally grateful. I'm sick of making extra columns to the right. Can you believe I'm developing an APP here in VBA and can't get this stupid formula?

453

I am fine with named ranges!

lvalnegri
2012-01-31, 08:20 PM
try this in cell B2 in ScreenCats =INDIRECT("MenuData!A"&MATCH(A2,MenuData!B:B,0))

Anne Troy
2012-01-31, 08:31 PM
You know, I don't know WHY that works. It looks VERY generic. I don't know WHY I couldn't make it work before. Thanks SOOOO much.

Ken: Thanks for giving us this forum.

Ken Puls
2012-02-03, 04:56 AM
ROFL!

Ivalnegri, thanks for joining and looking after Anne here. :)

Bob Phillips
2012-02-03, 11:15 AM
Anne, you know that INDIRECT is the last refuge of Excel scoundrels.

What is wrong with a simple INDEX/MATCH?

=INDEX(MenuData!A:A,MATCH(ScreenCats!A2,MenuData!B:B,0))

Anne Troy
2012-02-06, 04:28 PM
Okay, Bob. I defer to your formulaic knowledge. I have another one for you. I will post a new thread. I appreciate fast help. LOL

Bob Phillips
2012-02-07, 11:04 AM
Okay, Bob. I defer to your formulaic knowledge. I have another one for you. I will post a new thread. I appreciate fast help. LOL

Formulaic? Are you suggesting that I follow a preformed set of rules to answer these threads? :shocked: