VLookup Index Match first, second, third

[Mark F]

I have a sheet called "Top 100" where I want to vlookup or index match data from a sheet called "USCOTRN"

On the "Top 100" sheet, I want to find first appearance of the value that is in $B4 in Column A on "USCOTRN" sheet and display the value that is in Column F on "USCOTRN" sheet display in column BC (first cell is BC4) on the "Top 100" sheet

On the "Top 100" sheet, I want to find first appearance of the value that is in $B4 in Column A on "USCOTRN" sheet and display the value that is in Column D on "USCOTRN" sheet display in column BD (first cell is BD4) on the "Top 100" sheet

On the "Top 100" sheet, I want to find the second appearance (if there is one) of the value that is in $B4 in Column A on "USCOTRN" sheet and display the value that is in Column D on "USCOTRN" sheet display in column BE(first cell is BE4) on the "Top 100" sheet

On the "Top 100" sheet, I want to find second appearance of the value that is in $B4 in Column A on "USCOTRN" sheet and display the value that is in Column F on "USCOTRN" sheet display in column BD (first cell is BF4) on the "Top 100" sheet

If there are more than two appearance in Column A, id like to use columns BG onwards

I have tried with the code below, but not really getting anywhere, and not fully understanding how to get it to work

=INDEX(USCOTRN!$A$5:$D$1000,SMALL(IF(USCOTRN!$f$5:$f$1000=B4,ROW(USCOTRN!$f$5:$f$1000)-ROW(USCOTRN!A5)+1),1))

I'd appreciate any help to point me in the right direction

Thanks
Mark

 

[gaz_chops]

=INDEX(USCOTRN!$A$5:$D$1000,SMALL(IF(USCOTRN!$f$5:$f$1000=B4,ROW(USCOTRN!$f$5:$f$1000)-ROW(USCOTRN!A5)+1),1))

The 1 at the end tells small to return the 1st, you need to tell it to return the 1st 2nd 3rd etcso

=INDEX(USCOTRN!$A$5:$D$1000,SMALL(IF(USCOTRN!$f$5:$f$1000=B4,ROW(USCOTRN!$f$5:$f$1000)-ROW(USCOTRN!A5)+1),row(A1))

 

[Mark F]

=INDEX(USCOTRN!$A$5:$D$1000,SMALL(IF(USCOTRN!$f$5:$f$1000=B4,ROW(USCOTRN!$f$5:$f$1000)-ROW(USCOTRN!A5)+1),1))

The 1 at the end tells small to return the 1st, you need to tell it to return the 1st 2nd 3rd etcso

=INDEX(USCOTRN!$A$5:$D$1000,SMALL(IF(USCOTRN!$f$5:$f$1000=B4,ROW(USCOTRN!$f$5:$f$1000)-ROW(USCOTRN!A5)+1),row(A1))

Thanks

I've tried that formula, and Excel suggests there is an error in the formula and gives an amendment
=INDEX(USCOTRN!$A$5:$D$1000,SMALL(IF(USCOTRN!$F$5:$F$1000=B4,ROW(USCOTRN!$F$5:$F$1000)-ROW(USCOTRN!A5)+1),ROW(A1)))

This gives a #VALUE! error

Any ideas?

Mark

 

[gaz_chops]

Did you enter as an array? Enter with control, shift & enter.

 

[Mark F]

Did you enter as an array? Enter with control, shift & enter.

Not orginally, no
But when I did it came us with the same suggestion change, then #NUM! error

 

[gaz_chops]

The change is because i missed a bracket off the end, try

=INDEX(USCOTRN!$A$5:$D$1000,SMALL(IF(USCOTRN!$F$5:$F$1000=B4,ROW(USCOTRN!$F$5:$F$1000)-ROWS(USCOTRN!$A$1:$A$4)),ROW(A1)))

 

[Mark F]

The change is because i missed a bracket off the end, try

=INDEX(USCOTRN!$A$5:$D$1000,SMALL(IF(USCOTRN!$F$5:$F$1000=B4,ROW(USCOTRN!$F$5:$F$1000)-ROWS(USCOTRN!$A$1:$A$4)),ROW(A1)))

Thanks. That's the change it made, but still coming up as a #NUM! error

Could you advise me on why "ROWS(USCOTRN!$A$1:$A$4))" - to help me understand a bit more

Have I got the ranges right, or something else to think about?

 

[gaz_chops]

Just re-read your 1st post, the ranges are wrong -

=INDEX(USCOTRN!$F$5:$F$1000,SMALL(IF(USCOTRN!$A$5:$A$1000=B4,ROW(USCOTRN!$F$5:$F$1000)-ROWS(USCOTRN!$A$1:$A$4)),ROW(A1)))

The index range is what you want returning, match should match B4 to col A. You will need to change the index range to give you 1st, 2nd for each column you want to return.

The ROWS(USCOTRN!$A$1:$A$4)) compensates for the number of rows that are missing from the index/match range, in this case your data starts on row 5 so we adjust by 4 rows.

 

[Mark F]

Just re-read your 1st post, the ranges are wrong -

=INDEX(USCOTRN!$F$5:$F$1000,SMALL(IF(USCOTRN!$A$5:$A$1000=B4,ROW(USCOTRN!$F$5:$F$1000)-ROWS(USCOTRN!$A$1:$A$4)),ROW(A1)))

The index range is what you want returning, match should match B4 to col A. You will need to change the index range to give you 1st, 2nd for each column you want to return.

The ROWS(USCOTRN!$A$1:$A$4)) compensates for the number of rows that are missing from the index/match range, in this case your data starts on row 5 so we adjust by 4 rows.

Bingo! Sorted

Thanks so much for your help on this

Now that I've got this, I do have another stage which involves VBA...finding the last row (LR) in column A in Top 200, and using "LR" instead of the "1000". I can do the VBA for finding the LR, but tweaking the code below for "LR" is a little beyond me

LR= Range("a" & Rows.Count).End(xlUp).Row
Range("BC4").Select
    Selection.FormulaArray = _
        "=IF(ISERROR(INDEX(USCOTRN!R5C6:R10000C6,SMALL(IF(USCOTRN!R5C1:R10000C1=RC2,ROW(USCOTRN!R5C6:R10000C6)-ROWS(USCOTRN!R1C1:R4C1)),ROW(R1C1)))),0,INDEX(USCOTRN!R5C6:R10000C6,SMALL(IF(USCOTRN!R5C1:R10000C1=RC2,ROW(USCOTRN!R5C6:R10000C6)-ROWS(USCOTRN!R1C1:R4C1)),ROW(R1C1))))"

Mark

 

[gaz_chops]

You're welcome, unfortunately i'm not good with vb, hopefully someone else will jump in.