Best way to pull the correct data to a cell if multiple conditions are met

flapjack71

New Member
Joined
May 8, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I am trying to determine the best way to pull the correct data to a cell if multiple conditions are met. In the attached I tried using the IF AND formula to pull the correct data to E2 based on the values inside A2-D2, all of which have multiple drop down options. I thought I was on the right track since I got it to work in one instance, but I am unsure how to add the others. I'm thinking maybe a different formula is needed here?? Any assistance is appreciated!



=IF(AND(A2="YELLOW",B2="1/2IN",C2="END-BEHIND",D2="END-BEHIND"),N2)
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Welcome to the Board!

I am not sure I understand exactly what the issue is. Your formula looks valid. Can you tell us how it is not working?
Perhaps you can post some sample data along with desired results.

You post images using the tool mentioned here: XL2BB - Excel Range to BBCode
 
Upvote 0
I think the issue is not if the formula works or not, but how do they incorporate a lot of other combinations into the formula? I'd recommend not putting the combinations in the formula at all, since it would end up large and unwieldy. Instead, create a table with all the combinations, then you can look up the one you want like this:

Book1
ABCDEFGHIJK
1Dropdown1Dropdown2Dropdown3Dropdown4Result
2Blue1/2INEnd-BehindEnd-behind3Yellow1/2INEnd-BehindEnd-behind1
3Red1/2INEnd-BehindEnd-behind2
4Blue1/2INEnd-BehindEnd-behind3
5Yellow1INEnd-BehindEnd-behind4
6Red1INEnd-BehindEnd-behind5
7Blue1INEnd-BehindEnd-behind6
Sheet8
Cell Formulas
RangeFormula
E2E2=IFERROR(INDEX($K$2:$K$10,MATCH(A2&"|"&B2&"|"&C2&"|"&D2,$G$2:$G$10&"|"&$H$2:$H$10&"|"&$I$2:$I$10&"|"&$J$2:$J$10,0)),"No match")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
Eric W,
This seems like a much better way to tackle this! Thank you, I will give it a try!!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top