Returning 10 largest values when looking up in a list!

  • Hi there,


    I am trying to look up the top 10 values in California (or "CA" in the example) and then return them into a different sheet.


    In the attached example, I would like Sheet 1, F5, to have the largest value in California, having looked up from column H in sheet 2. I would then like the second, third, fourth etc. largest values down to 10.


    Sheet 2 - look up column H for "CA", return value in column C.


    Columns D and H both have formulas in.


    I've tried Large, IF. INDEX, Large, IF and can't get anything to work.


    Any help would be greatly appreciated!

    Files

    • Test Sheet.xlsx

      (1.32 MB, downloaded 35 times, last: )
  • Try something like this:


    =IFERROR(LARGE(IF(Sheet1!$H$2:$H$100="CA",Sheet1!$C$2:$C$100),ROWS($A$1:$A1)),"")


    Confirmed with CTRL+SHIFT+ENTER, not just ENTER and copied down.


    Adjust ranges as necessary.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • I didn't get an error but it didn't seem to work - the cell is just blank.


    What would using "ROWS" do? I agree with the first part of the formula (I had tried this already).

  • The ROWS() function is used to get a consecutively increasing number starting from 1 (for largest), 2 (for second largest), etc...


    Did you confirm the formula with CTRL+SHIFT+ENTER keys all hit at the same time? This should show { } brackets around the formula. Then copy it down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Still no luck, I did press C+S+E.


    The formula is working because if I amend "" to "9" at the end, a 9 appears. However, values from "CA" are just no pulling through.

  • Looks like you have some #N/A errors in column H...


    Try: =IFERROR(LARGE(IF(NOT(ISNA(Sheet2!$H$7:$H$206="CA")),Sheet2!$C$7:$C$206),ROWS($A$1:$A1)),"")


    confirmed with CSE keys, copied down.


    Or use the IFERROR() in the VLOOKUP in column H to return a 0 instead of error, then use my initial formula

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Well I feel like we're close! That formula is now pulling through the Top 10 locations, but not "CA" specific. I'm getting numbers, just not the right ones!

  • Update your formula in Sheet2, H7 to: =IFERROR(VLOOKUP(B7,Sheet3!A1:F41837,3,0),"") copied down.


    Then use: =IFERROR(LARGE(IF(Sheet2!$H$7:$H$206="CA",Sheet2!$C$7:$C$206),ROWS($A$1:$A1)),"") in Sheet1

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • See attached.


    I double-checked and it looks correct.

    Files

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • An alternative is to use Power Query. Here is the Mcode and I have attached the file for your review.