Multiple Criteria for equation in one cell

[rtroehrig]

So I am trying to factor in production rates and want to return a percentage based on that rate. The problem is the goal rate varies depending on data in two columns (equipment used, container type).

Column 1 could be:
None
Yes-Deaerator
Yes- RP Filter
Yes- Both

Column 2 could be:
Tote
Fluid Bag
EZ Tote
Drum
Keg
Pail
Can
1 lb Tub

That means there are a possible 24 different IF statements in there. This just feels entirely too cumbersome and I am sure would be hell to debug. Anybody have a better idea?

 

[Special-K99]

Re: There Has to be a Better Way- Multiple Criteria for equation in one cell

Use a table with all combinations listed then just use a VLOOKUP or LOOKUP or INDEX MATCH

 

[rtroehrig]

Re: There Has to be a Better Way- Multiple Criteria for equation in one cell

Looking back over the raw data, I realize there is a third variable with 4 choices. I'm no longer just horizontal and vertical.

 

[Special-K99]

Re: There Has to be a Better Way- Multiple Criteria for equation in one cell

So you now have 128 elements.
You can still use a table, just add an extra column, use copy paste to create the table
Base something on this, I havent created all the elements but you get the idea

Sheet 1, 3 column lookup, data starting in A1, with result from 4th column

  A B C D
1 A 1 1 1
2 B 1 2 2
3 A 2 1 3
4 B 2 2 5

Sheet2
A1 B
A2 2
A3 2
searching for B 2 2, result should be 5

in Sheet2!B1
=IFERROR(LOOKUP(2,(Sheet1!A$1:A$4=A1)/(Sheet1!B$1:B$4=A2)/(Sheet1!C$1:C$4=A3),(Sheet1!D$1:D$4)), "Not found")

 

[rtroehrig]

Re: There Has to be a Better Way- Multiple Criteria for equation in one cell

The above formula worked to an extent, but it was still kind of buggy and didn't pull completely correct. However, through this discussion and a talk with a co-worker, we were able to accomplish the task. It took the conversations to make the brain understand how simple this could be once the table was built.

Step 1- Build Table on a new worksheet. Using Copy and Paste, this went fairly quick.
Step 2- Add a column to the left of the value you want to return and run =CONCATENATE(A1,B1,C1) then fill the rest of the column
Step 3- In the column in which you want to display the returned value run =IFERROR(VLOOKUP(TRIM(J2)&TRIM(K2)&TRIM(L2),Table!D:E,2,0),"") of course your J2, K2, L2 would be different depending on your spreadsheet.