• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup value when condition is met

K

kaushik03

Member
Hi all excel experts,


I am not able to solve a problem which I will explain by using the following dummy dataset:


Problem1:

Assume I have a dataset from A1 to D5 as follows:


Name Value Status Reason

A 10 Yes X1

A 15 No Y1

A 10 No

A 15 Yes


I want excel to give me maximum value of col B(Value) when status = "Yes". I am able to do this by using the following array formula at B7:

{=MAX(IF(C2:C5=A7,B2:B5))}....(A7="Yes")...returns me 15


Now, in other cell, I want to fetch the corresponding value from status col (Col C) for the value 15, which in this case could be "No" or "Yes". But I want the formula to specifically give me "Yes" as I want excel to give me the maximum value (from "Value" or "B" col) when status = "Yes"


I am trying to get this done by using (I understand I might be doing this in wrong way):

{=INDEX(C2:C5,MATCH(A9,IF(B2:B5=B7,A2:A5),0))}....(A9="A", B7=15)


But I am getting "No"....(I understand formula is considering the first occurence where the corresponding value for 15 = No).


So in a word, for first formula I want 15 and for second formula I want "Yes" (instead of "No")


Problem2:

Assume I have a dataset from A11 to D14 as follows:


Name Value Status Reason

A 10 No X1

A 10 Yes

A 15 Yes


For first formula I want the second largest value from col "B" (e.i. 10) and for second formula I want "Yes"(for status col)


Please note that, since, I am posting this from my office network, I am not being able to upload the data file due to firewall policy.


Hope I am able to make my points clear.


Looking forward to your help


Kaushik
 
For Problem 1, wouldn't the formula always be "Yes"? The summary of your problem statement was to find the value in col C that corresponds to MAX value in col B where col C = "yes". This sounds like a logic loop.


Never the less, here's the array formula:

=INDEX(C:C,MAX(ROW(C2:C5)*(C2:C5=A7)*(B2:B5=MAX(IF(C2:C5=A7,B2:B5)))))


Problem 2, formula 1:

=LARGE(IF(C2:C5=A7,B2:B5),2)

Formula 2:

=INDEX(C:C,MAX(ROW(C2:C5)*(C2:C5=A7)*(B2:B5=LARGE(IF(C2:C5=A7,B2:B5),2))))


Note that you could replace the end of each INDEX formula with a cell reference to where you found the Max of 2nd largest value (and save some calc time).
 
Aw, thanks Kaushik. It just makes me happy knowing that I can help people. =)
 
@Luke M

Hi!

I was just wondering why did you answered this topic? Did you feel addressed within first line? :p

Regards!
 
Hi SirJB7,


Hope you are doing great.


Though you have addressed Luke in your post but I truly did not get your point (but I know you have a great sense of humor)!!


Regards,

Kaushik
 
Hi Luke,


One more query please. Hope I am not bothering you..


Say, I want to put another condition when name = A.


Then how can I adjust your Index formula to fetch yes value (while identifying max).


Assume the data set is like this:


Name Value Status Reason

A 10 Yes X1

A 15 No Y1

B 16 No

A 15 Yes


Looking forward to your reply.


Kaushik
 
Hi Kaushik ,


I am sure Luke will answer your question , but I have the same doubt that Luke had in his first post ; can you please answer this ?


If the formula =MAX(array) is entered as an array formula ( using CTRL SHIFT ENTER ) , it will return the maximum value of the specified array.


To take the example from your latest post , suppose column B ( B2:B5 ) has the values 10;15;16;15 , then =MAX(B2:B5) entered as a normal formula ( not an array formula ) , will return 16.


Now , if you add a criterion to this , saying that you are looking for the maximum value from column B where column A has "A" in it , then =MAX((A2:A5="A")*(B2:B5)) entered as a normal value will not give you a correct answer ; you need to enter it as an array formula , because it has to look at an array of values by processing two arrays. If you do this , it will return the correct answer 15.


So if you are looking for the maximum value from column B for the criterion that column C should be "Yes" , then =MAX(C2:C5="Yes")*(B2:B5)) entered as an array formula will return the correct answer of 15.


Given the above , what is the need for an additional formula to fetch the value "Yes" ? After all , that is a criterion within the MAX function ; it stands to reason that any value returned by the MAX function has to have a "Yes" value in column C.


Can you please clarify what you are looking for exactly ?


Narayan
 
Dear Narayan,


Thank you for your post regarding this.


I will try to explain in mored detail now with the end requirement.


Well, for the first formula, when I am looking for the maximum value from col B, I am not bothering about what the status is. I just want the maximum value of col B when name =A


Now in the next part, I want the formula to give me the status when Name = A, max value =15 (which we already fetched in other cell)


Now if there was no duplicate entries of Max value(of name = A) in col B (in this case there are two 15s), we would have only one status(either yes/no)


But if we have duplicate entries of Max values at col B and two different corresponding status(yes and no), I want the formula to fetch "Yes" for that max value(15) and that name("A")


The reason I want this is:

If status is "yes" I want the max value(in other cell) to be appended with a * (which we can do with simple IF formula). Like:


If(status="yes",maxvalue&"*",maxvalue)...something like this.


Taking the above duplicate enry scenario into consideration, if I do not get yes for that max value, the end formula will not append the max value with *


[Note: assume, there is batsman(Name=A)who scores 15 two times and all his other scores are below 15.Out of those two innings(when he sores 15),in one innings he was out(as "No" in our case) and in other innings he was not out(as yes in our case).So his best score would be 15 when he was not out)]


The same logic applies for second hlargest value


Am I able to make my points clear? Please let me know if you need any further explanation to help me in this.


Looking forward to your expert hand.


Best regards,

Kaushik
 
Hi Kaushik ,


Sorry but I am not clear still !


Either you are using one criterion , or you are using two crtieria or you are using three criteria ...


Adding each criterion is a matter of multiplying the logical arrays within the MAX function.


I do not understand why you should use two criteria while finding out the MAX value , and then use this value to retrieve the status of "Yes" ; why not use this as a third criterion ?


If you use the criteria of name = "A" and status = "Yes" together with the numeric values within the MAX function , you will get the maximum value which satisfies both criteria. Is this not what you want ?


Regarding the adding of an asterisk to the score , what does this have to do with the maximum value ? All NOT OUTs need to be marked as such using an asterisk next to the score.


Narayan
 
Narayan,


I am realyy sorry as, perhaps, I am not being able to clearly eaplain my query.


The formula that Luke suggested is to get the value in col C that corresponds to MAX value in col B where col C = "yes".

{=INDEX(C:C,MAX(ROW(C2:C5)*(C2:C5=A7)*(B2:B5=MAX(IF(C2:C5=A7,B2:B5)))))} [A7=yes)..

it gives me yes which I wanted to have.


Now I want to add another criterion to it where Name=A(in col A)[considering my latest dataset as follows:]

Name Value Status Reason

A 10 Yes X1

A 15 No Y1

B 16 No

A 15 Yes


so formula should get the exact match at row 5(where name="A", maxvalue=15)


Doest it make any sense?


Kaushik
 
Hi Kaushik ,


I think we are still going in circles !


Forget all Excel formulae to start with ; can I specify , in English , what you need ?


1. You have three or 4 or 5 or any number of columns of data.


2. One of these columns of data is numeric in nature ; let us say this is column X.


3. Suppose we start applying criteria , one after another. The first criterion is that column #1 ( I am deliberately writing #1 , this can refer to A , B , C or any other column from those many columns of data ) equals "status1" where status1 is some text which occurs in that column.


4. Suppose we apply a second criterion ; column #2 = "status2"


5. Suppose we apply a third criterion ; column #3 = "status3"


Now , a formula like : =MAX((column #1 = "status1")*(column #2 = "status2")*(column #3="status3")*(column of numeric data))


will return the maximum value from the column of numeric data , which satisfies the above 3 criteria.


Isn't this what you are looking for ?


Narayan
 
Narayan,


Probably I am not able to put my words correctly.


Can we have a verbal discussion on this(over phone)by any chance?


My number is 9916335653.

My email ID: pharmacyjukaushik@yahoo.com


Regards

Kaushik
 
@SirJB7

Why, indeed I did sir! =P

@kaushik

Don't mind SirJB7 too much, I admit his sense of humor is sometimes a little...quirky.


I'll admit that like Narayan, I don't fully understand the need, but here's the formulas as requested:


Problem 1, formula 1:

=MAX(IF((C2:C5=A7)*(A2:A5="A"),B2:B5))

prob 1, formula 2:

=INDEX(C:C,MAX(ROW(C2:C5)*(A2:A5="A")*(C2:C5=A7)*(B2:B5=MAX(IF((C2:C5=A7)*(A2:A5="A"),B2:B5)))))


Problem 2, formula 1:

=LARGE(IF((C2:C5=A7)*(A2:A5="A"),B2:B5),2)

Formula 2:

=INDEX(C:C,LARGE(ROW(C2:C5)*(A2:A5="A")*(C2:C5=A7)*(B2:B5=MAX(IF((C2:C5=A7)*(A2:A5="A"),B2:B5))),2))
 
Dear Luke,


Who told you do not get my requirement clearly!!


You just bang on target....solved the issue.


What more I can talk about you. You are just master of "MS EXCEL"


Hope one day I will be able to know at least 10% of what you discoverd about the mystry of excel.


Thank you for all your help Luke and looking forward to your continued help and support in this forum..


@ Narayan (another genious): Thank you for your continuous support in this...


Regards

Kaushik
 
You must log in or register to reply here.
Back
Top