Forum Discussion
Solved
IFS syntax Question #NAME? error
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 ...
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
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
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
