VLookup for the second instance

[excelformulas]

I'm trying to do a formula where I'm looking up the rate in a particular column in a table based on an ID number (first column). Each ID number shows up twice in teh table (once with $ amounts and then second with a rate). How do I do a vlookup formula to show me what is in column 3 the second time an ID is used? So If I did a vlookup for "123456789" in the below able for column 3 it shows 25,000,000 (which I need) but how do a separate formlua to show me 85.00 bps?

ID	Name	First	Next	Next
123456789​	Name 1	25,000,000	50,000,000
123456789​	Name 1	85.00 bps​	60.00 bps​	50.00 bps​
987654321​	Name 2	25,000,000	50,000,000
987654321​	Name 2	75.00 bps​	57.50 bps​	50.00 bps​

 

[KRice]

Welcome to the MrExcel board. What version of Excel are you using? You should update your profile to show that, as the version often determines which approach might be preferred.

 

[excelformulas]

Welcome to the MrExcel board. What version of Excel are you using? You should update your profile to show that, as the version often determines which approach might be preferred.

Sorry I'm new to posting here. I just updated it but i have Professional Plus 2016 at work.

 

[AhoyNC]

Maybe something like this:
The formula in cell H3 will work in an Excel version from 2010 up.
Formula in H5 will work in Excel 365

Book2
	A	B	C	D	E	F	G	H	I
1	ID	Name	First	Next	Next2			ID
2	123456789	Name 1	25,000,000	50,000,000				123456789
3	123456789	Name 1	85.00 bps	60.00 bps	50.00 bps			85.00 bps	Excel 2010 and up
4	987654321	Name 2	25,000,000	50,000,000
5	987654321	Name 2	75.00 bps	57.50 bps	50.00 bps			85.00 bps	Excel 365
Sheet1

Cell Formulas
Range	Formula
H3	H3	=INDEX(Table1[First],AGGREGATE(14,6,(ROW(Table1[ID])-ROW(A2)+1)/(Table1[ID]=$H$2),1))
H5	H5	=INDEX(FILTER(Table1[First],Table1[ID]=H2),2)

 

[KRice]

Here are a couple more options. One is close to that offered by @AhoyNC using INDEX/AGGREGATE. The other uses a conventional INDEX/MATCH, which finds the first match (you may already have that resolved), and then for the 2nd match (actually the last match), we can use a trick with LOOKUP to search for "2" in an array where it does not exist, so the function returns the last match. If you are certain that the last match in the table is the 2nd occurrence that you want, then this method should work too.

MrExcel_20240131_C (version 1).xlsx
	A	B	C	D	E	F	G	H	I
1	ID	Name	First	Next	Next		Lookup	123456789	123456789
2	123456789	Name 1	25,000,000	50,000,000			1st instance, col 3	25000000	25000000
3	123456789	Name 1	85.00 bps	60.00 bps	50.00 bps		2nd instance, col 3	85.00 bps	85.00 bps
4	987654321	Name 2	25,000,000	50,000,000
5	987654321	Name 2	75.00 bps	57.50 bps	50.00 bps
Sheet2

Cell Formulas
Range	Formula
H2	H2	=INDEX($C:$C,AGGREGATE(15,6,ROW(A:A)/($A:$A=$H$1),1))
I2	I2	=INDEX(C2:C5,MATCH($I$1,$A$2:$A$5,0))
H3	H3	=INDEX($C:$C,AGGREGATE(15,6,ROW(A:A)/($A:$A=$H$1),2))
I3	I3	=LOOKUP(2,1/(A:A=$I$1),C:C)

 

[kvsrinivasamurthy]

For amount

=INDEX($C$2:$C$100,AGGREGATE(15,6,ROW($C$2:$C$100)/($A$2:$A$100=$A$2),1)-ROW($C$1))

For rate

=INDEX($C$2:$C$100,AGGREGATE(15,6,ROW($C$2:$C$100)/($A$2:$A$100=$A$2),2)-ROW($C$1))

 

[excelformulas]

For amount

=INDEX($C$2:$C$100,AGGREGATE(15,6,ROW($C$2:$C$100)/($A$2:$A$100=$A$2),1)-ROW($C$1))

For rate

=INDEX($C$2:$C$100,AGGREGATE(15,6,ROW($C$2:$C$100)/($A$2:$A$100=$A$2),2)-ROW($C$1))

That works to get the 85 rate, but i need to copy this formula to others and have it pull the rate associated with that ID. SO how to i update the above formula to pull the correct rate if the IDs are different?

 

[KRice]

Is the first ID in row 2 or your worksheet? The formula I offered doesn't depend on the row location, but the formula you've mentioned in post #7 does. It assumes data begin in row 2. Then to accommodate matching IDs as you move down row by row, you'll want to change the $A$2 reference. Assuming the ID's are in column A and they begin on row 2, then $A$2 should be changed to $A2. The "1" that appears after it returns the 1st match. For the next match, the 1 would be changed to 2.

 

[kvsrinivasamurthy]

Can you upload a sample file.