Lookup the Second, the Third, or the Nth Value

RedMisfit

New Member
Joined
Feb 12, 2019
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
4,670
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Feb 12, 2019
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thanks for all your replies, I will try these.

I will also update my details
 

Watch MrExcel Video

Forum statistics

Threads
1,129,724
Messages
5,637,997
Members
416,998
Latest member
bbowne

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