[Solved] Formulas: If-This-equal-That-Then-This

  • How do I create the following formula:
    If C1, D1 and E1 equal 3 numbers in Q1, then T1=H, U1=H, V1=H, but if
    If C1, D1 and E1 equal 2 numbers in Q1 and equal 1 number in R1, then T1=H, U1=H and V1=T, but if
    If C1, D1 and E1 equal 2 numbers in Q1 and equal 1 number in S1, then T1=H, U1=H, and V1=C, but if
    If C1, D1 and E1 equal 1 number in Q1 and equal 2 numbers in R1, then T1=H, U1=T, and V1=T, but if
    If C1, D1 and E1 equal 1 number in Q1 and 1 in R1 and 1 in S1, then T1=H, U1=T, V1=C, but if
    If C1, D1 and E1 equal 1 number in Q1 and 2 in S1, then T1=H, U1=C, V1=C, but if
    If C1, D1 and E1 equal 3 numbers in R1, then T1= T, U1=T, V1=T, but if
    If C1, D1 and E1 equal 2 numbers in R1 and 1 in S1, then T1= T, U1=T, V1=C, but if
    If C1, D1 and E1 equal 1 number in R1 and 2 in S1, then T1=T, U1=C, V1=C, but if
    If C1, D1 and E1 equal 3 numbers in S1, then T1=C, U1=C, V1=C

  • I'd say you'd need to use a lot of columns to do the testing and then use those columns to determine what you want.


    Eg if something equals something let the cell equal 1 then add up the relevant cells and that will tell you how many matches.


    HTH

    There are three types of people in this world.
    Those who can count and those who can't.

  • Hi dk,


    I think Neale is right - a bit of testing is required, there is no easy formula to enter to test that many dependent conditions.


    have a look at the attachment and see if it gets you started.


    I used the MATCH function to test for C1, D1, and E1 in the 3 ranges and assigned values (1 for Q range, 10 for R range and 100 for S range) if a match was found. Next created a lookup table with the sum of the 1's, 10's and 100's as the lookup value and the associated values in T1, U1, and V1 in the table. Used the Vlookup function to populate the 3 cells.


    A bit cumbersome, I admit, but it does give the result.


    Hope this helps


    .....Ralph

  • The sheet you sent did not work. Please download my sample sheet. I used the following formula. It works if there is only one value in the Q, R and S cells but I have multiple values. I want to treat each digit in the cell as separate values and not one single value in a cell. Is there a command that will look into a cell for a single digit match with multiple digits in that cell. In my sample T7 works, but U7 and V7 gives me a false value. U7 should equal C, because 2 is in the S7 cell and V7 should equal T, because 6 is in the R7 cell.
    =IF(C8=Q7,"H",IF(C8=R7,"T",IF(C8=S7,"C")))
    What am I missing?


    Also, below I've updated the formula request:
    How do I create the following formula:
    If C1, D1 and E1 equal 3 numbers in Q1, then T1=H, U1=H, V1=H, but if
    If C1, D1 and E1 equal 2 numbers in Q1 and equal 1 number in R1, then T1=H, U1=H and V1=T, but if
    If C1, D1 and E1 equal 2 numbers in Q1 and equal 1 number in S1, then T1=H, U1=H, and V1=C, but if
    If C1, D1 and E1 equal 1 number in Q1 and equal 2 numbers in R1, then T1=H, U1=T, and V1=T, but if
    If C1, D1 and E1 equal 1 number in Q1 and 1 in R1 and 1 in S1, then T1=H, U1=T, V1=C, but if
    If C1, D1 and E1 equal 1 number in Q1 and 2 in S1, then T1=H, U1=C, V1=C, but if
    If C1, D1 and E1 equal 3 numbers in R1, then T1= T, U1=T, V1=T, but if
    If C1, D1 and E1 equal 2 numbers in R1 and 1 in S1, then T1= T, U1=T, V1=C, but if
    If C1, D1 and E1 equal 1 number in R1 and 2 in S1, then T1=T, U1=C, V1=C, but if
    If C1, D1 and E1 equal 3 numbers in S1, then T1=C, U1=C, V1=C


    Thanks

Participate now!

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