HLOOKUP 2nd instance...

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
Hi all

Is it possible to use HLOOKUP (or any other function) to return details of the 2nd (or 3rd) instance of a value?

E.G. If I have a list...

1 2 3 4 1 5 6 7 1
a b c d e f g h i

I want to have a section that displays the values of the first 3 instances something like:

(looking up "1")
1st = a
2nd = e
3rd = i

At the moment all I can do is just get the 1st value, it is no good changing the lookup table as I will never know the order of the list.

Thanks for your help in advance guys!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
Thanks for the reply Peter,

I have had a look at what you did before, and I have managed to convert it to work horizontally, however the problem I am going to have is that a lot of times my searches are not going to be using numbers - so SMALL won't work... e.g.

a b c a d e a
z y x w v u t

so for looking up a I want
1st = z
2nd = w
3rd = t

can it be done this way?

Thanks again!
 

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
Ahh forget that last message - I was being very dim indeed!

I have it now - thank you!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply Peter,

I have had a look at what you did before, and I have managed to convert it to work horizontally, however the problem I am going to have is that a lot of times my searches are not going to be using numbers - so SMALL won't work... e.g.

a b c a d e a
z y x w v u t

so for looking up a I want
1st = z
2nd = w
3rd = t

can it be done this way?

Thanks again!
Shouldn't be a problem. Here it is again with text values (still in vertical form). H2 formula copied across and down.

Excel Workbook
ABCDEFGHIJK
1JoeajsbName of Interest
2TombktcTombktc
3Joecluddmve
4TomdmveNo. of Rowsfoxg
5Kenenwf4hqzi
6Tomfoxg
7Kengpyh
8Tomhqzi
9Keniraj
Lookup All
 

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
Thanks again.

In case you're interested below is the formula I used. I have INDIRECT in there as my list is 8 rows of data next to a date (400 dates) and I want to be able to specify what date to look at - which is determined by a value in the cell C3615 (i.e. first date = 1, 2nd = 2, and so on)


=INDEX(INDIRECT("D"&($C$3615*8)-6&":IV"&($C$3615*8)),C$3617,SMALL(IF(INDIRECT("D"&($C$3615*8)-6&":IV"&($C$3615*8-6))=$B3619,COLUMN(INDIRECT("D"&($C$3615*8)-6&":IV"&($C$3615*8-6)))-3),C$3616))

Thanks so much - I've been scratching my head on this one for a while now, but this works perfectly!
 

Forum statistics

Threads
1,181,719
Messages
5,931,656
Members
436,797
Latest member
fullmetalpogi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top