Vlookup with multiple criteria & ranking

**fitzpatrickb** · 11-12-2009, 11:08 PM

I need help! Basically I need to create a array formula in Microsoft Excel (not allowed to use a macro) to do multiple functions. I have a list of data that can be updated regularly and can not be sorted. So additional line items can be added at any time (I have specified the max of data) I have created dummy data below. I want to find all the “Apple” Entries and then look up the highest B Value and Display Column C. And then I want to look up the next “Apple” with the 2nd highest B Value and Display Column C again.

Data Sheet
A B C
Apple 5 300
Banana 20 125
Cherry 25 200
Apple 10 210
Cherry 25 250
Apple 15 100

OUTPUT
Apple (Column C Data) (Highest B Value) == Apple 100 15
Apple (Column C Data) (2nd highest B Value) == Apple 210 15

(do not display next Apple Data)

I was using the following formula, but it does not select the largest value Column B first.
=IF(ISERROR(INDEX($A$2:$C$6,SMALL(IF(A$2:$C$6="Apple",ROW(A$2:$C$6)-ROW(A$2)+1,ROW(C$6,)+1),1),2)),"NONE",(INDEX(($A$2:$C$6,SMALL(IF(A$2:$C$6="Apple",ROW(A$2:$C$6)-ROW(A$2)+1,ROW(C$6,)+1),1),2)))

I also tried this formula, but the large function does not seem function correctly when there are duplicate values or when the value is blank. APPLE=Row A, BValues=Row B, and All Values = Row B & C.

=IF(ISNUMBER(MATCH("Apple",FRUIT,0)),VLOOKUP(LARGE((FRUIT="Apple")*(BVALUES),1),ALLVALUES,2,0),"")

Any ideas? I know this is complicated so feel free to ask questions I will do my best to explain.

**JeanRage** · 11-13-2009, 03:20 AM

Hi,

Attached is a step-by-step test for you ...

HTH

**fitzpatrickb** · 11-13-2009, 09:49 AM

This looks like it works, unfortunately I don't have the luxury to have data in multiple cells. So basically I need to come to the solution with 1 formula instead of two. Is there a way to nest these two formulas together? Any other ideas?

**JBeaucaire** · 11-13-2009, 11:20 AM

Try this, one cell formulas...

**WHER** · 11-13-2009, 12:31 PM

My two cents added. The formula consists of two concatenated parts, to give both results in one cell, as requested i think.
To complicate matters, i tweaked the given data slightly to force a triple ex-aequo on "Apples". In these circumstances, the formula returns the first occurring (in ascending row order) results.

**fitzpatrickb** · 11-13-2009, 03:03 PM

Wher---we are soo close!

It looks like this does everything correctly. The only issue I still need to determine how to handle is how to correctly account for when there is a Row for Apple with no data or "0" data in column B. With your current function it looks like it just grabs the next row of data (regardless if it is an "apple" or not).

I appreciate all the help. Keep the ideas coming =)

**WHER** · 11-13-2009, 05:28 PM

Getting closer?

**martindwilson** · 11-14-2009, 05:00 AM

I don't have the luxury to have data in multiple cells

. what's that mean?
you can always hide columns or do the calculations on another sheet and refer to it then hide sheet

**fitzpatrickb** · 11-20-2009, 03:57 PM

WHER--Great work! You got it!! Thanks so much. Sorry my reply is so late, I had to finalize some other projects before I could finish this one.

Martindwilson- I basically needed 1 formula to get my answer, one of the previous examples had 1 cell with 1 response and the 2nd sell referenced the 1st. I actually didn't need the data in the same cell, so with WHER's solution I just split out the data into seperate cells. (Hard to explain!).

I really appreciate all the help/time/effort for everyone. Have a great weekend!