# 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 42 times, last: )
• No I don't believe so - we run Excel 2013.

• 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!

• It's still just filling in the top location without taking into account "CA". Sorry!

• 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.