Using Multiple IF, AND & Or to Return Different Values

[Denny57]

I have need of a formula that can have several different cell values and values. There is a base cell which has just 2 possible values (Yes and No). Each of the other cells can have multiple values but only 1 will contain a specific value and when this is TRUE then a value of a specified corresponding cell should be returned. False = 0.

Example
Populate cell F5 with the identified value using an appropriate formula

Find the ONE cell in the range D1:D5 that contains the value X and return the corresponding cell value
B1 = Yes (True) {If FALSE return "0")
If cell D1 = X then return the value of Cell H1 or
If cell D2 = X then return the value of Cell H2 or
If cell D3 = X then return the value of Cell H3 or
If cell D4 = X then return the value of Cell H4 or
If cell D5 = X then return the value of Cell H5 or
If value X not found, return value "0"

I tried this shorter formula where B1 = "Yes" D1 = "Y", D2 ="X" and cell H1 contained "£1.00 and cell H2 contained £2.00.
=IF(AND(B1="Yes",OR(D1="X",H1),OR(D2="X",H2)),0)

The formula was accepted but returned "0"

Please can someone assist

Many thanks

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=IF(B1<>"Yes",0,INDEX(H1:H5,MATCH("X",D1:D5,0)))

 

[Denny57]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=IF(B1<>"Yes",0,INDEX(H1:H5,MATCH("X",D1:D5,0)))

Unfortunaltely my example was perhaps a little misleading as I gave details that were in a range, (also I should have shown the cells as being on the same row.

New Example

Find the ONE cell in the range D1:D5 that contains the value X and return the corresponding cell value
B1 = Yes (True) {If FALSE return "0")
If cell D1 = X then return the value of Cell A13 or
If cell E1 = X then return the value of Cell B21 or
If cell F1 = X then return the value of Cell D17 or
If cell G1 = X then return the value of Cell A25 or
If cell H1 = X then return the value of Cell C20 or
If value X not found, return value "0"

Apologies.

 

[Fluff]

Try

=IF(B1<>"Yes",0,CHOOSE(IFNA(XMATCH("X",D1:H1,0),0)+1,0,A13,B21,D17,A25,C20))

 

[Denny57]

Try

=IF(B1<>"Yes",0,CHOOSE(IFNA(XMATCH("X",D1:H1,0),0)+1,0,A13,B21,D17,A25,C20))

Thank you, this works fine. Now I have to apply this to about 150 different cells each with varying number of options.

 

[Fluff]

You're welcome & thanks for the feedback.

For future reference when you "Mark as solution" you are meant to select the post that helped you, not your post saying it worked. I have changed it for you this time.