SOLVED

IFS syntax Question #NAME? error

Occasional Contributor

I created a spreadsheet to collect times & material cost for the sake of updating our pricing in our paint department. I wanted the operators to select the paint of choice from a drop down menu, and by virtue of that selection, a cost per ounce would populate another field.  Here's the formula that I wrote:

=IFS(R5=Primer Yellow,AI5,R5=Primer Green,AI6,R5=Primer Grey,AI7,R5=Primer Black,AI8,R5=Topcoat White,AI9,R5=Topcoat Grey,AI10).  I get the #NAME? error.  What is wrong with my formula?

16 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Kerry_J 

Perhaps you mean texts

=IFS(R5="Primer Yellow",AI5,...
Hi Sergei,
You are the Bomb!!! It worked! Thank you so much!

@Kerry_J 

As @Sergei Baklan pointed out, the name strings have to be in quotes.  An alternative strategy often used in similar situations is to exploit the validation list to look up data from a corresponding list of data (cost in this case).

= XLOOKUP(selectedPaint,PaintColors,Cost)

 

Interesting Peter
I like that idea. I'm new to some of these formulas but I should be able to figure that out somehow. the current table I created for my spreadsheet looks like this which I think is a great candidate for your suggestion:
Paint Types Costs p/Oz.
Primer Yellow $1.07
Primer Green $1.05
Primer Grey $1.22
Primer Black $1.15
Topcoat White $1.56
Topcoat Grey $1.61

@Kerry_J 

A demonstration.

@Peter Bartholomew 

Thank you Peter.  I'll read up on "lookup" & "vlookup" to see how it's used.

Hi
I created a long IFS formula, i.e.: "=IFS(U11=AA5,AB5,U11=AA6,AB6,U11=AA7,AB7,U11=AA8,AB8,U11=AA9,AB9,U11=AA10,AB10,U11=AA11,AB11,U11=AA12,AB12,U11=AA13,AB13,U11=AA14,AB14,U11=AA15,AB15,U11=AA16,AB16,U11=AA17,AB17,U11=AA18,AB18,U11=AA19,AB19,U11=AA20,AB20)."

How can I paste it in various, non-sequential fields on my spreadsheet with out getting the table data locations skewed?

Thanks

@Kerry_J 

Try

 

=IFERROR(VLOOKUP(U11, $AA$4:$AB$20, 2, FALSE), "")

 

or

 

=XLOOKUP(U11, $AA$5:$AA$20, $AB$5:$AB$20, "")

 

The $ signs make the references to ranges in columns AA and AB absolute, i.e. they don't change when you fill or copy down.

Hi Hans
Thank you very much! I tried the XLOOKUP formula first because it appeared as if I understood it but it no worky for me. I then tried the IFERROR formula but it no worky either. I typed them verbatim as in your reply. Here's what my table of data looks like:
Paint Types Costs p/Oz.
Primer Yellow 02Y040A $0.97
Primer Green 44GN072 $0.86
Primer Green 44GN011 $1.32
Primer Green CA7255 $2.25
Topcoat White 03W127A #17925 $3.18
Topcoat Grey 03GY332 #36231 $2.35
Topcoat Black 01BK041 #37038 $1.52
Topcoat Black 08610KUZ #37038 $2.16
Topcoat Green 656-58-4424 $4.22
Topcoat Green P60-A $4.22
Topcoat Gray F-70-A $4.02
Topcoat Gray 03GY292 #36375 $1.41
Topcoat White 01W081F #17925 $0.95
Topcoat Gray 01GY085 #36231 $1.52
Topcoat Grey #36118 $2.34
Topcoat White 446-22-1000 $1.54

The cell "U11" is a drop down menu of "Paint Types" and based on the user's choice of Paint Types, the corresponding "Cost p/Oz" will populate into the cell that I choose - which is the cell that I want to learn how to formulate with out retyping the same long formula with different cells.
I can see that the didn't format as typed. Note that the Type of Paint (i.e.:Topcoat White 446-22-1000) is in column AA, and the Cost p/Oz (i.e.: $1.54) is in column AB.
Thanks again

@Kerry_J 

Here is a small demo workbook with the data that you provided. See if it helps.

Hey Hans
Thanks for the kind effort!
When deploying that formula on my spreadsheet, I get a #NAME? message in the cell. Also, while using your spreadsheet, I get the same message (in the "Cost p/Oz" cell) when selecting a paint type in any of the cells that contain a drop down menu item.

@Kerry_J 

I used XLOOKUP., which is on;y available in Microsoft 365 and Office 2021.

Here is a version using VLOOKUP.

I have Excel version 2019

@Kerry_J 

The version in my previous reply should work for you.

Hans:
I applied your formula - it worked!!! Thank you so much. I realize I have lots to learn in Excel
You are a very patient, tenacious, talented.