Apr 09 2022 09:31 AM
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?
Apr 09 2022 09:45 AM
SolutionApr 09 2022 10:12 AM
Apr 09 2022 10:15 AM
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)
Apr 09 2022 11:05 AM
Apr 09 2022 11:33 AM
A demonstration.
Apr 09 2022 01:08 PM
Thank you Peter. I'll read up on "lookup" & "vlookup" to see how it's used.
May 01 2022 02:47 PM
May 01 2022 02:54 PM - edited May 01 2022 02:54 PM
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.
May 02 2022 07:28 AM
May 02 2022 07:34 AM
May 02 2022 07:48 AM
Here is a small demo workbook with the data that you provided. See if it helps.
May 02 2022 10:11 AM
May 02 2022 10:19 AM
I used XLOOKUP., which is on;y available in Microsoft 365 and Office 2021.
Here is a version using VLOOKUP.
May 02 2022 11:01 AM
The version in my previous reply should work for you.
May 02 2022 11:03 AM
Apr 09 2022 09:45 AM
Solution