Microsoft Office excel 2007 - upgrade

I have two columns. First column contains either "X" or "Y" or "Z"second column contains a numeric value "1" or "2" or "3". In the third column I'd like to have the result be that IF "X" AND "1" then "1" OR IF "X" AND "2" then "2" OR IF "X" AND "3" then "3" OR IF "Y" AND "3" then "4" OR IF "Y" AND "2" then "5" OR IF "Y" AND "3" then "6", IF "Z" AND "1" then "6" OR IF "Z" AND "2" then "6" OR IF "Z" AND "3" then "7".

As afar as I can tell, this is what you are asking for. 1 - IF "X" AND "1" then "1"

2 - IF "X" AND "2" then "2"

3 - IF "X" AND "3" then "3"

4 - IF "Y" AND "3" then "4"

5 - IF "Y" AND "2" then "5"

6 - IF "Y" AND "3" then "6"

7 - IF "Z" AND "1" then "6"

8 - IF "Z" AND "2" then "6"

9 - IF "Z" AND "3" then "7"Look at items 4 & 6. You have Y and 3 twice, once returning 4 and once returning 6.

Sorry, it's one or the other.

Just checking: Items 6, 7 & 8 all return "6". Is that correct?

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Sorry that was a typo error, it should be read as 1 - IF "X" AND "1" then "1"

2 - IF "X" AND "2" then "2"

3 - IF "X" AND "3" then "3"

4 - IF "Y" AND "1" then "4"

5 - IF "Y" AND "2" then "5"

6 - IF "Y" AND "3" then "6"

7 - IF "Z" AND "1" then "7"

8 - IF "Z" AND "2" then "8"

9 - IF "Z" AND "3" then "9"

Assuming your letters are in A1 and your numbers are in B1, and based solely on the example data that you provided, this gets you what you asked for: =IF(A1="X", B1, IF(A1="Y", B1+3, IF(A1="Z", B1+6, "")))

Now, tell us what you

reallywant.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Hi DerbyDad03, Few changes in the requirement as given below:

If "X" & "1" then 1

If "X" & "2" then 0.5

If "X" & "3" then 0.5

If "Y" & "1" then 2

If "Y" & "2" then 1

If "Y" & "3" then 1

If "Z" & "1" then 4

If "Z" & "2" then 2

If "Z" & "3" then 1

If "NA" then 0

Didn't I say " Now, tell us what you really want"?Sometimes it doesn't help to simplify your examples because it leads to solutions that don't fit your needs.

BTW...instead of just posting the new requirements, you could have (should have) thrown a "Thanks" in there just to be polite.

Your last criteria isn't clear. Your first 9 criteria are checking values in 2 columns, you last one only checks 1. Which one?

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Hi, I will give you example for last value. Lets say in column A1 I have a value either of one "X" "Y" "Z" or "NA". If it is "NA" then in the result column it shoud give the output as "0". There is no need to check column B1 for this condition or we are not bothered about whatever is there in column B1.

My Apologize to u, if I have hearted to u by any means.

Here are 2 different options: A brute force IF statement that checks for each condition:

=IF(A1="X",IF(B1=1,1,IF(OR(B1=2,B1=3),0.5)),

IF(A1="Y",IF(B1=1,2,IF(OR(B1=2,B1=3),1)),

IF(A1="Z",IF(B1=1,4,IF(B1=2,2,IF(B1=3,1,0))),

IF(A1="NA",0,""))))The problem with such as long formula is that not only is it difficult to put together, it can be confusing to edit if you change your criteria (X, y or Z) or the desired results (.5, 1, etc.)

A better option might be this:

Put a table someplace in your worksheet that looks like this:

F G H I 1 X Y Z 2 1 1 2 4 3 2 0.5 1 2 4 3 0.5 1 1Then use a combination of IF's and VLOOKUP's to return your desired results. By using this method, you can simply change the values in your table and never have to touch the formula.

=IF(A1=$G$1, VLOOKUP(B1,$F$2:$I$4,2),

IF(A1=$H$1, VLOOKUP(B1,$F$2:$I$4,3),

IF(A1=$I$1, VLOOKUP(B1,$F$2:$I$4,4),

IF(A1="NA", 0, ""))))

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Thanks a lot dear...for your kind efforts towards my query. It's really helpfull. It's really gud to have ur kind of people who helps other's without knowing them.

Ask Your Question

Weekly Poll