# Lookup the Second, the Third, or the Nth Value

#### RedMisfit

##### New Member
I need some help to create a vlookup which will return the 2nd, 3rd vale etc when searching down a big list.

I have created a helper column in column B, and used this formula which allowed me to populate column F, searching against column E =IFNA(VLOOKUP(\$E2&COLUMNS(\$F\$1:F1),\$B\$2:\$C\$14,2,0),"")

The trouble I am having is using this across different workbooks, when I am trying to populate a spreadsheet with data using the helper column. I have tried adapting the forumla but it isn't quite working.

Thanks

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### jasonb75

##### Well-known Member
I would personally use INDEX combined with the FILTER function, which would eliminate the need for the helper column. Something like
Excel Formula:
``=IFERROR(INDEX(FILTER(\$C\$2:\$C\$14,\$B\$2:\$B\$14=\$E2),COLUMNS(\$F1:F1)),"")``
If you don't have the filter function then similar might be possible with other functions (please update your account details to show your version of excel so that we can see which functions you have).

#### etaf

##### Well-known Member
you could use
=IFERROR(INDEX(\$B\$2:\$B\$14,SMALL(IF(\$A\$2:\$A\$14=\$D2,ROW(\$A\$2:\$A\$14)-1,""),1)),"")
where B is the column to return a value from
A is the column to lookup
D2 is the value to lookup
and the 1 at the end is the Nth lookup - so 1 = 1st, 2 = 2nd

can also use a column reference if needed

see example here
with 2 methods of returning the Nth - either hard coding the number OR using a column Range

Lookup-Nth-Value-in-Excel.xlsx
ABCDEFG
1LookupReturn1st2nd3rd
2JLookup 1JLookup 1Lookup 2Lookup 3
3BLookup 1SLookup 1Lookup 2
4BLookup 2BLookup 1Lookup 2Lookup 3
5SLookup 1SLookup 1Lookup 2
6JLookup 2K
7LLookup 1LLookup 1
8JLookup 3GLookup 1
9GLookup 1MLookup 1
10MLookup 1
11JLookup 4
12JLookup 5
13BLookup 3
14SLookup 2
With Formula
Cell Formulas
RangeFormula
E2E2=IFERROR(INDEX(\$B\$2:\$B\$14,SMALL(IF(\$A\$2:\$A\$14=\$D2,ROW(\$A\$2:\$A\$14)-1,""),1)),"")
F2F2=IFERROR(INDEX(\$B\$2:\$B\$14,SMALL(IF(\$A\$2:\$A\$14=\$D2,ROW(\$A\$2:\$A\$14)-1,""),2)),"")
G2G2=IFERROR(INDEX(\$B\$2:\$B\$14,SMALL(IF(\$A\$2:\$A\$14=\$D2,ROW(\$A\$2:\$A\$14)-1,""),3)),"")
E3:G9E3=IFERROR(INDEX(\$B\$2:\$B\$14,SMALL(IF(\$A\$2:\$A\$14=\$D3,ROW(\$A\$2:\$A\$14)-1,""),COLUMNS(\$E\$1:E2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

#### RedMisfit

##### New Member
Thanks for all your replies, I will try these.

I will also update my details

Replies
3
Views
91
Replies
1
Views
454
Replies
3
Views
284
Replies
8
Views
99
Replies
1
Views
54

1,128,075
Messages
5,628,498
Members
416,322
Latest member
Corbett

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