Nested IFS statement

[GcOtt]

Greetings, Im having some trouble returning a value in my IFS statement and am hoping for some help.
I am trying to pull data from one column when a specific data set is present and in addition another column when an employee count is present - Here is what ive got and im getting "NA":
=IFS(A2="Random Text",IF(Master!C2="0-9",240))

Thanks in advance for your help here!

 

[etaf]

IFS () you usually show the TRUE
=IFS (test1, value1, [test2, value2], ...)

Looks like
=IF(A2="Random Text",IF(Master!C2="0-9",240, "FALSE condition"), "FALSE condition")
BUT if you want BOTH to be TRUE

Then
IF ( AND ( A2="Random Text", Master!C2="0-9" ) , 240, "false condition")

 

[GcOtt]

IFS () you usually show the TRUE
=IFS (test1, value1, [test2, value2], ...)

Looks like
=IF(A2="Random Text",IF(Master!C2="0-9",240, "FALSE condition"), "FALSE condition")
BUT if you want BOTH to be TRUE

Then
IF ( AND ( A2="Random Text", Master!C2="0-9" ) , 240, "false condition")

This is great thank you very much for your time!!

 

[GcOtt]

This is great thank you very much for your time!!

HI again, sorry Im not geting the function/value returned that is desired - in the above example I am looking to have 2 conditions directly related to each other produce 1 answer which I can then edit and reproduce in the formula. As an example if A2= random data 2 and C2 = 0-9 employees then return 240 as a value. Hope that makes sense - Im not having much luck with the above two options when trying to return this result - Thank you again for your help!!

 

[etaf]

not sure i fully understand
if A2= random data 2 and C2 = 0-9 employees then return 240
What exactly does
A2= random data 2
Mean
you have
A2="Random Text"
Does this mean that the cell A2 - is NOT blank ?

Do you want A2 AND C2 = to the text "0-9" or between 0 and 9

Can you give some more examples of whats in the cells to produce the result 240

 

[GcOtt]

not sure i fully understand
if A2= random data 2 and C2 = 0-9 employees then return 240
What exactly does
A2= random data 2
Mean
you have
A2="Random Text"
Does this mean that the cell A2 - is NOT blank ?

Do you want A2 AND C2 = to the text "0-9" or between 0 and 9

Can you give some more examples of whats in the cells to produce the result 240

Thanks very much for replying - As an example in A2 you would find a description of a service so "ABC service" C2 would have a different range of employees 1-10, 11-20 etc. What id like the formula to do is see if the values in A2 and C2 are present then return a response - in this if ABC service and 1-10 are present then return 240. Hope that helps clear it up thanks again for your time!

 

[etaf]

are there a range of correct values ,
ABD service
or 5-8
are they OK

If you a few different values, then perhaps a Table of values to lookup

So
what are all the possible valid values for A2 ?
what are all the possible valid values for C2 ?

Or if easier
what are NOT the possible valid values for A2 ?
what are NOT the possible valid values for C2 ?

Excel needs to see specifics
It is possible to use wild cards in the lookup/search

 

[GcOtt]

Yes actually a table of values is what I am aiming for so if a table of values is present in the "A" column and a different table of values is present in "C" column - I need a formula that can understand when a value in column A and C are present then a different result is provided in Column D. An example is
Column A: 2021, Column C: 0-9 Column D formula should report back 240
OR
Column A: Add on Column C: 1000-1500, Column D formula should report back 600. Unsure on how I would set this up as a table of values to lookup?

Does that make sense? Thanks again for the help!

 

[joeino]

Can you please provide values found in Cols A and C? With the example you provided, it is hard to establish the result you are looking for.

 

[GcOtt]

HI thanks for the reply - the values in Column C are as follows: 0-9,10-99, 11-499,500-1499,1500-4999 Values in column A are text values ABC company, DEF whatever etc