Obtaining a value based on two preceeding conditions

[Kris K]

I need to create a formula where a cell will show me a value based on the values in two preceding cell. I have been looking throughout this forum including archives and could not find anything similar to this. I am currently working on a model and the below formula would address most of my formulas needed to complete my model.


I would appreciate any help I can get in creating a formula that would help me achieve the below intent.


i.e.


- if cell A1 has a text value of L and cell B1 has a value containing text HPR from data range D1:D10 (whole value would actually be HPR01) then it will show a value of 30 in C1


- if cell A1 has a text value of L and cell B1 has a value containing text MPR from data range D1:D10 (whole value would actually be MPR01) then it will show a value of 15 in C1


- if cell A1 has a text value of L and cell B1 has a value containing text LPR from data range D1:D10 (whole value would actually be LPR01) then it will show a value of 5 in C1


- if cell A1 has a text value of L and cell B1 has a value containing text HPC from data range D1:D10 (whole value would actually be HPC01) then it will show a value of 30 in C1


- if cell A1 has a text value of L and cell B1 has a value containing text MPC from data range D1:D10 (whole value would actually be MPC01) then it will show a value of 15 in C1


- if cell A1 has a text value of L and cell B1 has a value containing text LPC from data range D1:D10 (whole value would actually be LPC01) then it will show a value of 5 in C1


- if cell A1 has a text value of S and cell B1 has a value containing text HPR or MPR or LPR or HPC or MPC or LPC from data range D1:D10 then it will show a value of 0 in C1


And vice versa for S


- if cell A1 has a text value of S and cell B1 has a value of HPR from data range E1:E10 (whole value would actually be HPR01 )then it will show a value of 30 etc…


- if cell A1 has a text value of L and cell B1 has a value containing text HPR or MPR or LPR or HPC or MPC or LPC from data range E1:E10 then it will show a value of 0 in C1

 

[Luke M]

Can you explain what you mean by "cell B1 has a value containing text HPR from data range D1:D10"? Does B1 have the text "HPR01" or does D1:D10? Is D1:D10 the list of acceptable words?


When you say vice versa scenario, what happens if conflict/both conditions met? (I.e., A1 = S, B1 has a value of "HPR" from both D1:D10 and E1:E10 (this may be resolved by answer to first question)?

 

[Kris K]

sorry let me paint the scenario a little bit better.


A1 is a dropdown list which gives me option L or S only.


B1 is a dependent dropdown list on A1. If in A1 I choose L then in B1 a dropdown list only from D1:D10 shows up from which I can choose a value. If S is chosen in A1 then in B1 a dropdown list appears from range E1:E10 only.


Values in both ranges are part text and part number i.e. HPR01...HPR05 etc. The reason I have these two lists even though they share same codes is because for S list there are more of HPRs and others codes HPR01-HPR07 etc. than in L list HPR01-HPR05, etc. And I don’t want the extra options to show up in B1 drop down list when L is chosen in A1.


Each code under respective L or S represents a scenario for either S or L. I may still change the codes as later I will have to do conditional formatting where I will have the background change to a certain color depending if its HPR for L or S (another reason I need to keep these as separate lists).


So what I need is for C1 to give me respective figure as I noted above based on the scenario that plays out in the two previous cells. If I need to create another range list for the values then so be it but I honestly am lost when it comes to putting it together into formula.


Sorry for the confusion when writing part text and not clarifying the reason for two ranges…. I hope this clarifies things. I’m novice when it comes to excel formulas beyond the basics.

 

[Kris K]

ok the below formula is what i thought might resolve my problem i.e. if a particular text appears in B9 then a specific assigned value will appear in C9 but it doesn't work. What am I doing wrong???? The B9 is a drop down list from which I can pick specific text code and all I want is for the associated number value to appear in C9.


=LOOKUP(B9,{LHR01,LHR02,LHR03,LHR04,LHR05,LMR01,LMR02,LMR03,LMR04,LMR05,LMR06,LMR07,LMR08,LMR09,LLR01,LLR02,LHC01,LLC01,LLC02,LLC03,SHR01,SHR02,SHR03,SHR04,SHR05,SMR01,SMR02,SMR03,SMR04,SMR05,SMR06,SMR07,SMR08,SMR09,SMR10,SLR01,SLR02,SLR03,SLR04,SHC01,SMC01,SMC02,SLC01,SLC02,SLC03},{"30","30","30","30","30","15","15","15","15","15","15","15","15","15","5","5","30","5","5","5","30","30","30","30","30","15","15","15","15","15","15","15","15","15","15","30","15","15","5","5","5"})

 

[Hui]

Kris

It should be

=LOOKUP(B9,{"LHR01","LHR02","LHR03","LHR04","LHR05","LMR01","LMR02"},{30,30,30,30,30,30,30})

but extended for the full list

 

[Hui]

Kris

It should be

=LOOKUP(B9,{"LHR01","LHR02","LHR03","LHR04","LHR05","LMR01","LMR02"},{30,30,30,30,30,30,30})

but extended for the full list


Also your two {arrays} must have the same number of elements, yours dont


However I think the following is probably what your after

=IF(UPPER(MID(B9,2,1))="H",30,IF(UPPER(MID(B9,2,1))="M",15,5))

which is based on your list

 

[Kris K]

Perfect! The second formula is exactly what I was looking for. Thank you!!!


I'm learning a lot by trial and error and it's great to have this forum as a resource.


One thing I just learned that in order for the lookup to work, values must also be ascending both in vector and array otherwise the returned values may not match up and that's ascending whether is alphabetically or numerically.


I don't want to further bother you with this since it's resolved but for my learning benefit can you explain what does the 2 and 1 mean after B9 in the formula, and why dont you repeat the whole step for 5 i.e. ="L",5 but write it the way you did which it worked? (i think i just got it the last 5 is 'otherwise choose this value', right?)... tried looking up upper and mid formulas on excel (using 2010) couldn't make sense of it.


Thank you so much for your assistance!

 

[Hui]

The format of Mid is

=Mid(Text, Start Pos, Length)

This means go to the Start Position in the Text and get the Length No of characters

eg:

=Mid("Excel",2,3) will return "xce"

2nd position is x

xce has length 3

 

[Kris K]

Thank you. I'm learning a lot here.

 

[Luke M]

Just fyi, UPPER and LOWER functions force text into upper and lowercase text, which is sometimes needed in certain comparisons (always in VB). I don't think Hui's formula actually needs it, as XL tends to think:

="A"="a"

is True. Just my 2 cents.