Calculating Variable Locality Surtax Up to a Maximum (Table or Long IF-ELSE Stmt)

We have various localities with varying local surtax amounts collectable on the first 5,000 in sales and I am looking for a good way to have that tax automatically computed based upon both the locality name, it's corresponding tax rate up to the maximum limit if possible. I've a list of the localities, rates and maximum for this surtax to help explain it.
I have written if statements that handle the situation, say for all sales of 5,000 or less, but it is long and when I add the variable for sales in excess of 5,000 it gets unwieldly. Does anyone know how to use the XLOOKUP or other table LOOKUP function to solve this need? Thank you.

LocalSurtaxTable.JPG

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

in C27

=IF(C25>5000,VLOOKUP($A27,$B$41:$D$50,3,FALSE),VLOOKUP($A27,$B$41:$D$50,2,FALSE)*C25)

An alternative formula for cell C27:

=MIN(5000,C25)*VLOOKUP(A27,$B$41:$C$50,2,FALSE)