# Formula with lots of conditions

1. ## Formula with lots of conditions

I would like to enter a formula into one cell that says:

if B24 = "White" and B19= "White" then £12. If B24= "White" and B19= "Black" then £20. If B24="White" and B19= "Gold" then £25. If B24= "Gold" and B19= "Gold" then £12. if B24= "Gold" and B19 = "White" then £16 etc etc.

Is this possible?

2. ## Re: Formula with lots of conditions

if(and(B24 = "White",B19= "White"),12,If(and(B24= "White",B19= "Black"),20,If(and(B24="White",B19= "Gold")25,If(and(B24= "Gold",B19= "Gold"),12,if(and(B24= "Gold",B19 = "White"),16,if(and(etc,etc)))))))

Thank you!!

4. ## Re: Formula with lots of conditions

hi amy1, welcome to the forum. do up a table like i did in G19:J23. in column I, i did a concatenation of Column G & H. then put in the values in Column J. your formula would be in D21:
=IFERROR(VLOOKUP(B24&B19,\$I\$19:\$J\$23,2,0),"")

5. ## Re: Formula with lots of conditions

Hi and welcome to the forum

daffodil's suggestion will work just fine for you, if you dont have too many more conditions to test for. Although you can have a ton of nested IF()'s, another option might be to create a table of combinations/values and then use a vlookup to return what you want. Something like...

WhiteWhite...12
WhiteBlack...20
WhiteGold...25
GoldGold...12
etc

edit: ben beat me to it (see, told you it was a good idea lol)

6. ## Re: Formula with lots of conditions

Thank you all for your replies. I have used daffodil's suggestion but for another very similar formula in the same spreadsheet there are lots more conditions so I'd like to use the table for this. I am a little confused how to enter the formula. I have made the table on another sheet within the same workbook and named the table (if that helps). Can you please break down how to enter the formula?

7. ## Re: Formula with lots of conditions

Do you have a sample to share?

8. ## Re: Formula with lots of conditions

It's very similar to the above conditions just with more of them. Have added a screenshot of the start of the table, will be much more in it but you get the idea.

I just need to understand how to construct the formula to add it to the cell.

Screen Shot 2013-08-12 at 19.05.12.png

9. ## Re: Formula with lots of conditions

Originally Posted by benishiryo
hi amy1, welcome to the forum. do up a table like i did in G19:J23. in column I, i did a concatenation of Column G & H. then put in the values in Column J. your formula would be in D21:
=IFERROR(VLOOKUP(B24&B19,\$I\$19:\$J\$23,2,0),"")
Can someone please explain what the red section is. I don't know how to write the formula.

What are the \$ symbols and what do the last two numbers (2,0) refer to?

10. ## Re: Formula with lots of conditions

The dollar symbols means that the cell references (row and/or column) will not change when the formula is copied down or across. As this refers to a table containing data to be looked up, you always want to refer to the same references when the formula is copied. The third parameter, 2, means return the corresponding data from the second column of the table, i.e. look for the combined value B24&B19 within column I (cells 19 to 23), and if a match is found then return the data from the next column (J). The zero at the end of the formula (could also be FALSE), means that you are looking for an EXACT match only.

Hope this helps.

Pete

11. ## Re: Formula with lots of conditions

Originally Posted by Pete_UK
The dollar symbols means that the cell references (row and/or column) will not change when the formula is copied down or across. As this refers to a table containing data to be looked up, you always want to refer to the same references when the formula is copied. The third parameter, 2, means return the corresponding data from the second column of the table, i.e. look for the combined value B24&B19 within column I (cells 19 to 23), and if a match is found then return the data from the next column (J). The zero at the end of the formula (could also be FALSE), means that you are looking for an EXACT match only.

Hope this helps.

Pete
Thank you so much for the help! I understand it completely now.

Amy

12. ## Re: Formula with lots of conditions

Well, that's good to hear, Amy.

VLOOKUP is a very useful function in Excel, so it will be very beneficial to understand how it works.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1