VLOOKUP OFFSET 1-10th row

[toughie]

Hi there,

Im trying to create a formula to look up the 1st, 2nd 3rd... occurences off "CHUCKER" using vlookup heres my formula for the first row:

=VLOOKUP("CHUCKER",'[1 (version 1).xlsb]Pivot'!$1:$1048576,2,FALSE)

anyone know the formula to return the 2nd and 3rd occurences? Any help would be hugely appreciated.

 

[AlanY]

have a look here

How to vlookup find the first, 2nd or nth match value in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

 

[dermie_72]

if the chucker's are all in order then you could use Index Match with an increase in the match number based on the output request.
A1:A6 = Chucker
B1:B6 = Data (Different values so you can confirm)

C1 = "Chucker"
D1 = The number you want to find IE 4
e1 = "=INDEX(B:B,MATCH(C1,A:A,0)+(D1-1),0)"

This only works if the field you are looking for is sorted. Otherwise you would need to create a macro or function to return the correct value.

 

[steve the fish]

=IFERROR(INDEX($B$1:$B$1000,SMALL(IF($A$1:$A$1000="Chucker",ROW($A$1:$A$1000)),ROWS($A$1:A1))),"")

You may need to enter with CTRL-SHIFT-ENTER. The formula gives the first hit. Drag it down and you get the 2nd, 3rd etc.