IF Statement with multiple criteria

[kenadams378]

Morning,

I have an IF statement that i am using to try populate a cell based on whether a cell contains yes or no.

If the cell contains Yes i need it to return column A or B depending on which one has an error or not but to look at both A & B in the formula

Column C is the column with Yes or NO & column D is the column to be populated with the source data

I've attached a test example.

Any help would be appreciated

[duanzhuanming]

Hi,
Try this formula:

Please Login or Register  to view this content.

[Pete_UK]

I suppose you want to show the entry which is not in error. If so, use this formula in D2:

Please Login or Register  to view this content.

then copy down. This will return a blank if both A and B are showing errors, as well as column C not showing "yes".

By the way, you should set your calculation option on the Formulas tab to Automatic.

Hope this helps.

Pete

[kenadams378]

Thank you both very much!!

Really appreciate your time to help

[Fotis1991]

Thank you both very much!!

Really appreciate your time to help

This is very nice and correct.

Just a query. In this your own thread...

http://www.excelforum.com/excel-form...88#post3238488

..didn't anyone spend time to helps you?

[kenadams378]

What would i need to do if there was a 3rd column to look to?

I've attached a further example

[kenadams378]

Bump - No Response

[Pete_UK]

Not too difficult to extrapolate from what you were given before. Put this in E2:

=IF(D2="YES",IF(ISERROR(A2),IF(ISERROR(B2),IF(ISERROR(C2),"",C2),B2),A2),"")

then copy down. Change your calculation option to automatic.

Hope this helps.

Pete

[kenadams378]

You are a star!!!

[kenadams378]

I have one last query if you are able to help,

I have a similar situation but it uses a lookup too?

The example i have attached, in the yellow cell this should read no after looking across thge 4 columns to find the data.

Is it possible to add more than 2 criteria to the formula?

The formula i have currently is;

Please Login or Register  to view this content.

[Pete_UK]

You could do it like this:

Please Login or Register  to view this content.

but as you are only looking at one column you can use MATCH instead of VLOOKUP like this:

Please Login or Register  to view this content.

Hope this helps.

Pete

[kenadams378]

Thank you very much again!!

Very pleased with the result