Vlookup Multiple Results

[coreyjames]

I have looked all over for a way to return a vlookup with multiple results. I have found some answers but I am looking for a different format. Please see my example below.

A1: B1:
Corey 27
Jeff 25
Taj 45
Keith 24
Corey 32

Most solutions I have found will post the data as follows:

Corey 27
32

The way I have my spreadsheet setup this will not work. I want to be able to get the results like this:

Corey 27 (1st occurance)
Tal 45
Keith 24
Corey 32 (formulas knows it already posted 27 so now it posts 32)

Thanks!

 

[T. Valko]

I have looked all over for a way to return a vlookup with multiple results. I have found some answers but I am looking for a different format. Please see my example below.

A1: B1:
Corey 27
Jeff 25
Taj 45
Keith 24
Corey 32

Most solutions I have found will post the data as follows:

Corey 27
32

The way I have my spreadsheet setup this will not work. I want to be able to get the results like this:

Corey 27 (1st occurance)
Tal 45
Keith 24
Corey 32 (formulas knows it already posted 27 so now it posts 32)

Thanks!

You didn't tell us what the lookup criteria is.

 

[coreyjames]

The lookup criteria would be the name. The age would be the results.

 

[T. Valko]

The lookup criteria would be the name. The age would be the results.

Let's assume:

• A2:A6 = names
• B2:B6 = age
• lookup names starting in E2

In the formula I use the following defined named ranges:

• Names
• Refers to: =$A$2:$A$6
• Age
• Refers to: =$B:$B

Enter this array formula** in F2:

=INDEX(Age,SMALL(IF(Names=E2,ROW(Names)),COUNTIF(E$2:E2,E2)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down as needed.

 

[coreyjames]

For some reason this is not working. It is grabbing the age from the wrong names and if say my name repeats it just says #ref!

 

[T. Valko]

For some reason this is not working. It is grabbing the age from the wrong names and if say my name repeats it just says #ref!

Here's a small sample file that demonstrates this.

zzzcoreyjames.xls 14kb

http://cjoint.com/?ADDrokgV7dD

 

[coreyjames]

Excellent I see how this works but if my raw data is on tab 2 and I am using this on tab1 for results will it still work?

 

[T. Valko]

Excellent I see how this works but if my raw data is on tab 2 and I am using this on tab1 for results will it still work?

Yes!

Just make sure the sheet name is used in the named ranges (which Excel usually does by default).

 

[coreyjames]

Hmm.. This works great on your example but I cannot get it to work properly on my worksheet.

on tab 1 of my worksheet my lookup starts on g7 and my formula that you are helping with starts on i7.

tab2 is my raw data with the case codes (Name) in B2-B3554 and my procedures (Age) are in G2-G3554

 

[T. Valko]

Hmm.. This works great on your example but I cannot get it to work properly on my worksheet.

on tab 1 of my worksheet my lookup starts on g7 and my formula that you are helping with starts on i7.

tab2 is my raw data with the case codes (Name) in B2-B3554 and my procedures (Age) are in G2-G3554

What is the REAL sheet name of "tab2"?