Somethink like VLOOKUP, but to return the nth value that matches, not the first or last?

[fishoutofwater]

In column A I have a list of names. Each name appear between 5 and 30 times. In column B I have random stuff. Let's say A1 through A12 says, "FRED" and A13 through A20 says "Sally". If in column B next to all Fred names are items you'd find at a grocery store, and in column B next to all Sally names are cities in North America-- let's say the cities are in this order: Portland, New York, Atlanta, Miami, etc., I want to do something like this in C1:

=vlookup("SALLY",A1:B100,2,0)

but I want to specify: "the 3rd entry" in which case I'd get "Atlanta" or I might want to specify the 4th Sally entry, in which case I'd expect a return of "Miami"

I am guessing that VLOOKUP is not really what I want. But Mathc doesn't seem quite right either. Is there a way to do this vlookup idea, but request then nth value when the lookup value appears multiple times in the list?

Thanks for any help.

 

[EXCEL-lence]

The first thing that springs to mind to me is to order your list of names, then use a formula to add numbers next to this, then use a concatenation of the name and the number, to lookup what you wish

e.g.

A B C D
FRED 1
FRED , if(a2=a1,b1+1,1) gves FRED2 in column C and in column D = North America
FRED
FRED

then use the lookup on column C .. vlookup(FRED&nth, C:D, 2, false)

Let me know if this works...

Please support my own website if you like my answers and sign up to my own forum.

www.excel-lence.info

Cheers

EXCEL-lence

 

[Aladin Akyurek]

In column A I have a list of names. Each name appear between 5 and 30 times. In column B I have random stuff. Let's say A1 through A12 says, "FRED" and A13 through A20 says "Sally". If in column B next to all Fred names are items you'd find at a grocery store, and in column B next to all Sally names are cities in North America-- let's say the cities are in this order: Portland, New York, Atlanta, Miami, etc., I want to do something like this in C1:

=vlookup("SALLY",A1:B100,2,0)

but I want to specify: "the 3rd entry" in which case I'd get "Atlanta" or I might want to specify the 4th Sally entry, in which case I'd expect a return of "Miami"

I am guessing that VLOOKUP is not really what I want. But Mathc doesn't seem quite right either. Is there a way to do this vlookup idea, but request then nth value when the lookup value appears multiple times in the list?

Thanks for any help.

Let E1 house SALLY and F1 4 (Nth entry).

G1, control+shift+enter, not just enter:

=IF(COUNTIF($A$1:$A$100,E1)>=F1,INDEX($B$1:$B$100,
   SMALL(IF($A$1:$A$100=E1,ROW($A$1:$A$100)-ROW($A$1)+1),F1)),"")

 

[EXCEL-lence]

 

[fishoutofwater]

Thanks for the thought, this solution would not really work for what I need to accomplish with the sorting and adding columns.. Is there a more elegant way to do it, like with a single function like "VLOOKUP"?

 

[fishoutofwater]

Thanks Aladin. I think that will work. I am going to try it now. Thanks so much for the help!!

 

[Aladin Akyurek]

Thanks for the thought, this solution would not really work for what I need to accomplish with the sorting and adding columns.. Is there a more elegant way to do it, like with a single function like "VLOOKUP"?

See also post #3.

 

[T. Valko]

In column A I have a list of names. Each name appear between 5 and 30 times. In column B I have random stuff. Let's say A1 through A12 says, "FRED" and A13 through A20 says "Sally". If in column B next to all Fred names are items you'd find at a grocery store, and in column B next to all Sally names are cities in North America-- let's say the cities are in this order: Portland, New York, Atlanta, Miami, etc., I want to do something like this in C1:

=vlookup("SALLY",A1:B100,2,0)

but I want to specify: "the 3rd entry" in which case I'd get "Atlanta" or I might want to specify the 4th Sally entry, in which case I'd expect a return of "Miami"

I am guessing that VLOOKUP is not really what I want. But Mathc doesn't seem quite right either. Is there a way to do this vlookup idea, but request then nth value when the lookup value appears multiple times in the list?

Thanks for any help.

Try this...

Data:

Book1
	A	B
1	Name	Item
2	Sally	Grapes
3	Sally	Celery
4	Sally	Chicken
5	Fish	Trout
6	Fish	Bass
7	Biff	Gold
8	Biff	Silver
9	Biff	Diamonds
10	Biff	Pearls
Sheet1

Criteria:

Book1
	D	E	F	G
1	Name	Count	Instance	Item
2	Biff	4	2	Silver
Sheet1

In the formulas I use the following named ranges:

• Names
• Refers to: =Sheet1!$A$2:$A$10
• Item
• Refers to: =Sheet1!$B:$BD

D2 = the name to look up

Enter this formula E2. This will return the count of records for the lookup name.

=COUNTIF(Names,D2)

F2 = the instance number of the lookup name

Enter this array formula** G2 to return the nth item for the lookup name.

=IF(OR(F2="",F2>E2),"",INDEX(Item,SMALL(IF(Names=D2,ROW(Names)),F2)))

** 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.

Here's a small sample file that demonstrates this.

ZZZfish1.xls 15kb

http://cjoint.com/?AHCbeelvV20

 

[Aladin Akyurek]

Thanks Aladin. I think that will work. I am going to try it now. Thanks so much for the help!!

You are welcome. Thanks for providing feedback.