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.
Hi,
Attached is a step-by-step test for you ...
HTH
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?
Try this, one cell formulas...
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
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.
Last edited by WHER; 11-13-2009 at 12:34 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 =)
Getting closer?
. what's that mean?I don't have the luxury to have data in multiple cells
you can always hide columns or do the calculations on another sheet and refer to it then hide sheet
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks