Lookup the Second, the Third, or the Nth Value

[RedMisfit]

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.

Can anyone offer any advice or better ways to go about this?

Thanks

 

[jasonb75]

I would personally use INDEX combined with the FILTER function, which would eliminate the need for the helper column. Something like

=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]

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
	A	B	C	D	E	F	G
1	Lookup	Return			1st	2nd	3rd
2	J	Lookup 1		J	Lookup 1	Lookup 2	Lookup 3
3	B	Lookup 1		S	Lookup 1	Lookup 2
4	B	Lookup 2		B	Lookup 1	Lookup 2	Lookup 3
5	S	Lookup 1		S	Lookup 1	Lookup 2
6	J	Lookup 2		K
7	L	Lookup 1		L	Lookup 1
8	J	Lookup 3		G	Lookup 1
9	G	Lookup 1		M	Lookup 1
10	M	Lookup 1
11	J	Lookup 4
12	J	Lookup 5
13	B	Lookup 3
14	S	Lookup 2
With Formula

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,""),1)),"")
F2	F2	=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,""),2)),"")
G2	G2	=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,""),3)),"")
E3:G9	E3	=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]

Thanks for all your replies, I will try these.

I will also update my details