Search code examples
excelexcel-formulaexcel-2007

find corresponding values, when lookup criteria is identical

I have a small table in Excel in which I am trying to find first and second largest in column PTS using formula =LARGE(Q15:Q18,1) and =LARGE(Q15:Q18,2), all works fine. Now comes the part where I am getting into a problem, if 2 or more values are equal like 5 in my case, I need to find the corresponding value from column F to be able to check which is greater by looking at the second criteria. I have tried using INDEX MATCH combination formula to lookup the corresponding value but it always returns the matching value of the first large number in the column, which in my example is number 9 from the F column.

Question, how can I lookup the first and second largest from column PST and their corresponding values from column F?

F   A   GD      PTS
7   9   -2      2
8   8   0       4
9   9   0       5
10  8   2       5

Please help

Solution

  • If you have the following sample:

    enter image description here

    In the following Cells add the function:

    D9  -> =LARGE(D1:D7;1)
D10 -> =COUNTIF(D1:D7;"="&D9)           And Autocomplete
F1  -> =IF(G1="";"";1)                  Only This
F2  -> =IF(G2="";"";MAX($F$1:$F1)+1)    And Autocomplete
G1  -> =IF($D$10>1;IF(D1=$D$9;ADDRESS(COLUMN(D1);ROW(D1));"");"")    And Autocomplete
G9  -> =IFERROR(VLOOKUP(ROW(H1);$F$1:$G$7;2;);"")                    And Autocomplete

    I Explain:
    In D10 i found if there was more that 1 LARGE. YES ? -> in the cell G... I search If the Row are LARGE. YES ? in the column F I put the Index to compile G9 ...In G9 ... I copy the address (VLOOKUP)
    After I can use a Indirect / Row function to use the data...
    It's a little bit complicated, but work... Oviusly HIDE the columns of intermediate Search...