# Using Multiple IF, AND & Or to Return Different Values

#### Denny57

##### New Member
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Fluff

##### MrExcel MVP, Moderator
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
Excel Formula:
``=IF(B1<>"Yes",0,INDEX(H1:H5,MATCH("X",D1:D5,0)))``

#### Denny57

##### New Member
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
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Try
Excel Formula:
``=IF(B1<>"Yes",0,CHOOSE(IFNA(XMATCH("X",D1:H1,0),0)+1,0,A13,B21,D17,A25,C20))``

#### Denny57

##### New Member
Try
Excel Formula:
``=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

##### MrExcel MVP, Moderator
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.

Replies
6
Views
82
Replies
4
Views
82
Replies
4
Views
155
Replies
5
Views
238
Replies
3
Views
45

### Forum statistics

Threads
1,127,107
Messages
5,622,777
Members
415,927
Latest member
vedasinternational

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back