May 28 2021 09:08 AM
Hi, I'm trying to pull the tax rate from another sheet based on a match from a cell in the sheet I'm working from. So if the cell reference is found in the other sheet I want it to return the tax rate to the cell with the formula in it. I've tried XLOOKUP and Index/match and get nothing but the formula as a result. I don't even get an error.
=XLOOKUP(IF F2='Table 1'!A2:D589,Table 1'!,5)
F2 is the city I want the tax rate for
The range if in Table 1 which holds city, county, tax, etc
Column 5 is where the tax rate exists in Table 1
I'm retired and rusty, lol. Please help?
May 28 2021 09:40 AM
Assuming the data is converted to Excel Tables
=XLOOKUP([@OrderShipCity], TaxRateTbl[CITY], TaxRateTbl[RATE],"Not found")
will at least give a result. Only the first occurrence of the city is looked up and some cities are missing.
May 28 2021 09:54 AM
May 28 2021 10:01 AM
SolutionHere's a simpler formula that will work
=VLOOKUP(F2,'Table 1'!$C$2:$D$591,2,0)
As noted by Peter and Sergei, though, you have some cities missing from your table, so you'll get an #NA on occasion
You also need to convert the format of that cell (the whole column actually) to "number" or "general"--as you posted it, so presumably as you have it, it's formatted as "Text" and then will do exactly what it's been doing, show you your formula as text.
May 28 2021 10:48 AM
May 28 2021 10:01 AM
SolutionHere's a simpler formula that will work
=VLOOKUP(F2,'Table 1'!$C$2:$D$591,2,0)
As noted by Peter and Sergei, though, you have some cities missing from your table, so you'll get an #NA on occasion
You also need to convert the format of that cell (the whole column actually) to "number" or "general"--as you posted it, so presumably as you have it, it's formatted as "Text" and then will do exactly what it's been doing, show you your formula as text.