Using IF and OR functions

[Bridgman]

Hi all, I have a formula that is working fine for a single cell but as soon as I try to add another cell to the formula it fails.

The objective is to return a different value depending on which cell in a row contains an X (i.e. C3 =1, E3 = 2, G3 = 3 etc etc). If an X is entered into a cell I would like the corresponding value to populate into the total at the end of the row (X3) for example. I have the fixed values in the adjacent columns for the formula to reference (D3=1, F3=2 etc)

I have used the below formula to get the first cell working correctly, but as soon as I add the additional cells it does not work.

=IF(C3="X",D3,0)

So an X in C3 populates X3 with a value of 1 which is the fixed content in cell D3

However if I use

=IF(OR(C3="X",D3,0)(E3="X",F3,0))

it continues to populate X3 with a 1 even if E3 has the X in it, despite the fixed value of F3 being a 2.

I have also tried

=IF(C3="X",D3,0)*(OR(E3="X",F3,0))
But no joy.

Any suggestions welcomed.

 

[Yongle]

Is this what you want?

=IF(C3="X",D3,IF(E3="X",F3,IF(G3="X",H3,"X not found")))

It returns the first found "X"

 

[Yongle]

another way

=INDEX(C3:H3,MATCH("X",D3:G3,0))

Also returns first found "X"

 

[Bridgman]

Thank you Yongle, I will try and let you know how I get on

 

[Bridgman]

Works a treat. thank you

 

[Yongle]

Could also use this
=INDEX(3:3,,MATCH("X",3:3,0)+1)