Conditional formulas based on which item is chosen in a drop-down menu

Question

Hi,&nbsp;I have 6 different tests in a drop-down menu. Each test has different result requirements in multiple columns. I need to figure out how to make an "Acceptable" column with formulas that give a result of "Yes" if the results in the columns match the requirements for the test that is chosen, no if not.&nbsp;So say the requirments for Test A are &gt;=0.08 in B, &gt;=0.5 in C, &lt;0.08 in D, and &lt;0.008 in E. I would put the formula in F to say yes or no.I know I could do an IF formula but how do I connect the formula to the particular test? I would need a formula to say if Test A is selected for A12, then B12 must be &gt;=0.08, C12 must be&gt;0.5, D12 must be &lt;0.08, and E12 must be &lt;0.008.The requirements for Test B are &gt;=0.09 in B, &gt;=0.5 in C, &lt;1 in D, and &lt;0.2 in E.If they choose Test B in A12, then we need to make sure the requirements in corresponding cells (B12, C12, D12, E12) are being used to judge acceptability to say Yes or No in F12.Any help is much appreciated!&nbsp;

varsdigital · Answer

With 6 tests I'd keep the limits out of the formula and put them in a small table, one row per test. Say H1:L1 holds the headers Test, Min B, Min C, Max D, Max E, and rows 2 to 7 hold your six tests (Test A: 0.08, 0.5, 0.08, 0.008; Test B: 0.09, 0.5, 1, 0.2; and so on). The names in column H have to match the drop-down items exactly.Then in F12:=IF(A12="","",IF(AND(B12&gt;=VLOOKUP(A12,$H$2:$L$7,2,FALSE),C12&gt;=VLOOKUP(A12,$H$2:$L$7,3,FALSE),D12&lt;VLOOKUP(A12,$H$2:$L$7,4,FALSE),E12&lt;VLOOKUP(A12,$H$2:$L$7,5,FALSE)),"Yes","No"))Each VLOOKUP pulls the limit for whichever test is picked in A12, and AND only gives Yes when all four checks pass. The outer IF leaves F blank until a test is chosen. Copy it down the column.We tried it on your numbers: 0.08 in B passes Test A but fails Test B (minimum 0.09), which is the switch you're after. If one of the other tests needs a check the other way round (a maximum in B, say), give it its own column in the table rather than another IF. Also, your post has C at &gt; 0.5 in one place and &gt;= 0.5 in another; change &gt;= to &gt; if a result of exactly 0.5 should fail.(Drafted with AI assistance; formulas tested before posting.)

m_tarler · Answer

This might be a little overkill but here is a nice function that will do what you want... (note if you copy this multiline formula you must paste it directly into the formula bar or into a cell while in edit mode, if you only select a cell and click paste the lines will spill onto other cells)=LET(in,A56:E62,table,A51:E52,
test, TAKE(in,,1),
data, DROP(in,,1),
luTest, TAKE(table,,1),
luData, DROP(table,,1),
testset, CHOOSEROWS(luData,XMATCH(test,luTest)),
BYROW(MAP(data,testset,LAMBDA(a,b,COUNTIF(a,b))),PRODUCT))and here it is tested in a sheet.&nbsp; I have a lookup table for what the TEST limits are INCLUDING the &gt;,&lt;,&gt;=,&lt;= conditionals and then pass them to a COUNTIF to interpret those conditionals and use PRODUCT to make sure all of them pass.&nbsp; Below shows the lookup table in A50:E52 (i.e. your 2 tests A &amp; B but could include many more) and the rows of test data in A55:E62 and then the formula is in F56 and spills down the column for every row in the test set:so how it works is in row 1 is the input for the data to test (in) and the lookup table (table),then rows 2-5 just break it up into data vs lookup test namesthen row 6 will create a table of lookup values based on the test names chosenthen row 7 does the comparison using MAP to pass each data value with the corresponding condition to the COUNTIF and then do the PRODUCT across each row.

Forum Discussion

Conditional formulas based on which item is chosen in a drop-down menu