# Nested IF statement to return a value based on multiple criteria

1. ## Nested IF statement to return a value based on multiple criteria

Hello Excel Gurus,

I am trying to return a value based on multiple criteria using a nested IF statement. Basically, I have a table that contains costs for a direct mail campaign, and I am trying to return the cost of the program based on the values that can be selected in two separate cells which both have drop-down lists. There are 20 different possible values that can be returned based on the possible combination of selections in those two separate cells.

If you look at the attached spreadsheet, the cell that I am trying to return the costs to is cell B4 (Total Program Costs). The possible values that can be returned are all in column G.

The statement should return values based on two criteria:

1) It should compare the value of B2 with the values in Row F
2) It should also compare the value of B3 and compare with the values in Row A.

It should then return the value based on the way the table is set up.

So, if a user selects "Long Mailer" in cell B3, and 100,000 in B2, then cell B4 should return a value of \$43,300.

If a user selects "Jumbo Postcard" in cell B3, and 25,000 in B2, then cell B4 should return a value of \$11,500.

I attempted to solve the problem using a really long nested IF statement using the AND command as well. I got an error message in Excel telling me that I had exceeded the max amount of levels in a nested statement. I am assuming there is a more elegant way to do this, but I cannot figure it out.

Help!

Thanks.

2. ## Re: Nested IF statement to return a value based on multiple criteria

I think that you want to use a lookup function and I think that it can best be shown in the video (very long) referenced below.

Alan

3. ## Re: Nested IF statement to return a value based on multiple criteria

Hi,

Give this a try.

Cheers,

4. ## Re: Nested IF statement to return a value based on multiple criteria

This worked perfectly. If it's too much trouble, do you think you can explain what you did?

Thanks so much!

Originally Posted by ConneXionLost
Hi,

Give this a try.

Cheers,

5. ## Re: Nested IF statement to return a value based on multiple criteria

Hi again, sorry for the delay.

Two things were changed in your workbook.

First, I changed the items in your "Mailer_Type" list to ensure an exact match was possible with the items in the 'Direct Mail'!A19:A45 range.

Second I added the formula:

Please Login or Register  to view this content.
to the 'Direct Mail'!B4 cell.

This formula is thoroughly explained in this SUMPRODUCT article.

Cheers,

6. ## Re: Nested IF statement to return a value based on multiple criteria

Hi All - help please!
In the attached spreadsheet, if any of the assets rows 7 to 29 have a number in column K, I would like that information in the relevant row to show in rows 71 to 80 in order.

any recommendations?

7. ## Re: Nested IF statement to return a value based on multiple criteria

Hi azb0105,

With regard to Forum Rule #2, I recommend you start your own post.

Cheers,

##### Users Browsing this Thread

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