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

 

[Aladin Akyurek]

The ROW(A1) bit is not a good idea. In which do you install the formula you now have?

 

[Mark F]

The ROW(A1) bit is not a good idea. In which do you install the formula you now have?

Hi Aladin

The cell would be cell BC4

Thanks
(and apologies for starting a new question for the VBA part)

Mark

 

[Aladin Akyurek]

Hi Aladin

The cell would be cell BC4

Thanks
(and apologies for starting a new question for the VBA part)

Mark

This anchoring should work... Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(USCOTRN!$F$5:$F$1000,SMALL(IF(USCOTRN!$A$5:$A$1000=B4,ROW(USCOTRN!$F$5:$F$1000)-ROW(USCOTRN!$F$5)+1),ROWS($BC$4:BC4))),"")

 

[gaz_chops]

The ROW(A1) bit is not a good idea. In which do you install the formula you now have?

Hi Aladin, can I ask why?

 

[Aladin Akyurek]

Hi Aladin, can I ask why?

Inserting rows in front of the first data row will lead to wrong retrieval... Why do you think some seasoned posters do anchor such formulas to a cell say A3 with ROWS($A$3:A3) or COLUMNS($A$3:A3) etc?

 

[Eric W]

This isn't tested, but to change your VBA code to insert the last row, should be something like:

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

 

[gaz_chops]

Inserting rows in front of the first data row will lead to wrong retrieval... Why do you think some seasoned posters do anchor such formulas to a cell say A3 with ROWS($A$3:A3) or COLUMNS($A$3:A3) etc?

Thanks for the explanation.