Trouble with an IF/OR/AND formula

[micksandals]

Hi,


I have a table, and I need a formula that determines whether a value is displayed based on the other entries in the table:


Column A = Name

Column B = an INDEX-MATCH lookup to another sheet, based on the value in Column A


The formula in B returns a 0 if the lookup value is not found, otherwise it will return a number between 1 and 90.


I need to amend the formula in B5 so that it only returns a value if B1 OR B2=0, OR if there are entries in A3 or A4 and the related value in B3 or B4 is 0.


So in this first example, B1 and B2 <> 0, A3 and A4 are empty, so B5 returns 0.


| A | B

1 | Jerry | 45

2 | George | 90

3 | | 0

4 | | 0

5 | Elaine | 0*


In this second example, B1 and B2 <> 0, but A3 is not empty and B3=0, so B5 returns its value (60).


| A | B

1 | Jerry | 45

2 | George | 90

3 | Cosmo | 0

4 | | 0

5 | Elaine | 60*


So in other words, B5 should = 0 unless one of the values (not empty cells) in A1:A4 returns a value of 0 in Column B.


Does that make sense?

 

[NARAYANK991]

Hi Mick ,


I am not sure I have understood your problem correctly , but will this do the job ?


=IF(OR(D2=0,E2=0,AND(F2<>"",G2=0),AND(H2<>"",I2=0)),"value",0)


where value is the formula which uses the INDEX/MATCH to retrieve the value from the other sheet.


Narayan