# HLOOKUP 2nd instance...

#### cob98tp

##### Board Regular
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.

### 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

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
Ahh forget that last message - I was being very dim indeed!

I have it now - thank you!

#### Peter_SSs

##### MrExcel MVP, Moderator

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
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!

#### stanleydgromjr

##### Banned
Peter_SSs,

Very nicely done.

I can use this.

Have a great day,
Stan

Replies
1
Views
75
Replies
5
Views
157
Replies
5
Views
446
Replies
7
Views
83
Replies
0
Views
163

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.

### Which adblocker are you using?

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

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