Forum Discussion
Solved
IFS Function Multiple Criteria
I am trying to write an IFS formula (for a tax spreadsheet) where I type in the name of a city and it automatically enters the sales tax rate for that particular locality. Here is an example:
If I type CLEVELAND in A1 then I want B1 to say 8%
If I type CHICAGO in A2 then I want B2 to say 10%
and if an Unknown City is typed in or it is left blank I want it to say "Enter Sales Tax Rate"
I researched the formula and thought I was putting it in in correctly but it keeps saying #VALUE! and I completely baffled.
This is how I wrote the formula:
=IFS(B2="Elyria",6.5%, "Cleveland", 8%, "West Salem", 6.5%, "Hudson", 6.75%, "Toledo", 7.75%, TRUE, "Enter Sales Tax Rate")
I hope someone can help!
=IFS(A1="Elyria",6.5%,A1= "Cleveland", 8%, A1="West Salem", 6.5%, A1="Hudson", 6.75%, A1="Toledo", 7.75%, TRUE, "Enter Sales Tax Rate")You can try this formula.
A variant with XLOOKUP and a dedicated lookup table.
=XLOOKUP(D2,city,rate,"Enter Sales Tax Rate")=IFS(A1="Elyria",6.5%,A1= "Cleveland", 8%, A1="West Salem", 6.5%, A1="Hudson", 6.75%, A1="Toledo", 7.75%, TRUE, "Enter Sales Tax Rate")You can try this formula.
- I left it as IFS (Because I didn't see it was changed to WENNS until just now) and put in the A1 before each city and it worked perfectly! Thank you so much!
