How to Look up Single list value & Return multiple corresponding values

**LAB2007** · 01-08-2007, 12:29 PM

Hello,

I'm looking for a way to have excel look up one value (from a list) and return multiple corresponding values.

For example, using the following data:

A1: Smith
A2: Jones
A3: Doe
A4: Clark
A5: Smith

B1: 509A5
B2: 837T7
B3: 123C6
B4: 833K2
B5: 509A5

C1: Blue
C2: Green
C3: Yellow
C4: Red
C5: Orange

If I wanted to know the corresponding values for "Smith" (cells A1 and A5), I need a formula that will give me the values in B1, C1, B5 and C5(ie: 5009A5, Blue, 509A5 and Orange).

I tried the MS Office online example (using the Ashish, Sanjay, Pongal list), but I cannot get the formula to work (for reference, the MS Office example is found at http://office.microsoft.com/en-us/ex...260381033.aspx

Any thoughts, suggestions?

Thanks!
L.

**oldchippy** · 01-08-2007, 12:50 PM

With Smith in cell A9

=VLOOKUP($A$9,A1:B7,2,FALSE) in B9

=VLOOKUP($A$9,A1:C7,3,FALSE) in C9

=VLOOKUP($A$9,A1:D7,3,TRUE) in D9

**starguy** · 01-08-2007, 01:00 PM

Hi

better is to use absolute references with lookup_range and relative reference with lookup_value (in case if you want to drag the formula)

=VLOOKUP(A9,$A$1:$C$7,2,FALSE) for getting value from col B
=VLOOKUP(A9,$A$1:$C$7,3,FALSE) for getting value from col C
if A9 contain Smith

Regards.

**jasoncw** · 01-08-2007, 01:15 PM

I don't think VLOOKUP will help him, since the first column is not sorted, and he wants multiple results. That link to the Microsoft site works for me. Did you hit CTRL + SHIFT + ENTER after the formula?

**LAB2007** · 01-08-2007, 01:42 PM

Thanks for the replies. The VLOOKUP will not work to return multiple values and my data will not be sorted.

What does CTRL + SHIFT + ENTER do after entering a formula?

**jasoncw** · 01-08-2007, 02:00 PM

Originally Posted by LAB2007

What does CTRL + SHIFT + ENTER do after entering a formula?

Certain functions (including the array function used above) will not work in a standard formula. CTRL + SHIFT + ENTER enters brackets around the entire formula {}.

**Lynztrev** · 05-04-2012, 07:49 AM

Originally Posted by jasoncw

Certain functions (including the array function used above) will not work in a standard formula. CTRL + SHIFT + ENTER enters brackets around the entire formula {}.

Fantastic, thanks for this, it was a massive help!

**ar2210** · 08-22-2013, 09:04 AM

Originally Posted by jasoncw

I don't think VLOOKUP will help him, since the first column is not sorted, and he wants multiple results. That link to the Microsoft site works for me. Did you hit CTRL + SHIFT + ENTER after the formula?

Hi

I wish to do the same thing, but this time have dates in column A

Nov-13 AA
Nov-13 BB
Dec-13 CC
Feb-14 DD

I want to get all corresponding values if it is Nov-13 etc.This formula works for names/strings but not for formatted dates! Can anyone help please.

Many Thanks
AR

**arlu1201** · 08-22-2013, 09:49 AM

ar2210,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.