Using Multiple IF, AND & Or to Return Different Values

Denny57

New Member
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Try
Excel Formula:
=IF(B1<>"Yes",0,CHOOSE(IFNA(XMATCH("X",D1:H1,0),0)+1,0,A13,B21,D17,A25,C20))
 
Solution

Denny57

New Member
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

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

Which adblocker are you using?

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
Top