# Limiting an Array range in =LARGE formula

• Hello - Long time lurker, second time poster.

I am trying to see if I can limit an array range when LARGE asks for the array based on a header and column categories; returning Vendor Name / Feb Amount / Δ% for the highest Δ% in category selected by drop-down.

I've attached testing file. Options I've considered -

• Do I need to make source data a Table?
• Is there a better way to arrange source data to make formula's easier?

Any help would be appreciated.

Thank you,
-Kyle Rupp

## Files

• Hi

I can't upload the workbook now. I will upload the workbook later today.

Create three ranged names as below:

Use below formula against each name

[F]
tblData: Sheet1!\$C\$1:INDEX(Sheet1!\$C\$1:\$G\$1000,COUNTA(Sheet1!\$C:\$C),5)

rngCat : =INDEX(tblData,0,2)

rngPercent =OFFSET(Sheet1!\$C\$1,MATCH(Sheet1!\$J\$1,rngCat,0)-1,4,COUNTIF(rngCat,Sheet1!\$J\$1))
[/F]

Then create the following formula on your sample sheet
L21 - enter as an array formula (CTRL+SHIFT+Enter]

[F]

N21 = LARGE(rngPercent,\$K21)

L21 = OFFSET(rngPercent,MATCH(N21,rngPercent,0)-1,-4)

M21= VLOOKUP(L21,tblData,MATCH(\$M\$20,\$C\$1:\$G\$1,0),FALSE)

[/F]

Regards

Maqbool

• Hello,

Attached is your Test file with the Array Formula ...:wink:

You do not need to modify anything ... in order to get the formula working ... :smile:

Hope this is line with your expectations ...

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Carim & Maqbool -

Thank you both for your help. I've gained a lot of insight from your answers. As always, the community sincerely appreciates your help!

Best,
-Krupp825

Thanks for both your Thanks ...AND for the Like ... :smile:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hello Carim -

I have a question for you on this same topic. I see that the LARGE formula currently does not recognize negative values, and just cuts them out. Is there a way to fix this?

I slightly modified the original file you provided in the following ways to demonstrate how the LARGE formula is not picking up negative values -

• Added IFERROR at front of each formula to display " - " upon error; more user friendly.
• Expanded list to display 9 items (enough to encompass all items in each category)

Using category SHEEP as an example which holds a total of five items, the formula displays the three with positive values, but not the two items assocaited with negative values. Can we edit the formula to include all items, including ones associated with negative values?

Thanks!
-Kyle

## Files

• Hello,

Since you are moving from Top 3 ... to Top 9 ... :wink:

Attached is your modified test file ...

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Wow, this is perfect. Thank you very much! Hope you have a great rest of your week!

• Thanks a lot for your very kind Thanks ..AND for the Like ...:smile:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!