Formula Needed

Micah Turner

Good day everyone. I am writing a sheet for our proposals to our customers. I would like to have the tax column fill in the appropriate tax for each line item. I have a second sheet with the tax rate for each county in our state. What I need is a formula that will calculate the tax for the appropriate city that is entered. I have included screenshots of each sheet.

Kelly Moore

Hey @Micah Turner

An Index/Match is the formula you need since you only require one criteria (the city) to match against. If you had multiple criteria to match against (for instance, city and state), the formula choice would be an Index/Collect. I am going to wrap the Index/Match in an IFERROR function. In the event there is not a match between the two sheets you can designate what you want to happen. For instance, you could have it add a zero, a blank cell or any phrase you desire. I will use the word "No Match". Should you want to trigger an automatic notification to you when there is a No Match, the sheet would be set up to do so.

=IFERROR(INDEX({Tax Collector Sheet Column 2}, MATCH([Project City]@row, {Tax Collect Sheet Column 1},0))

You will not be able to copy paste this formula into your sheet as you need to physically insert the cross sheet references into your formula. You do this from the formula window and click the blue link "Reference another Sheet". Once the other sheet opens, you will select the entire column (eg, "Tax Collector Sheet Column 2".

Let me know if you have any trouble with this formula

Kelly