2

I've created a quote form, and in one cell (C6:H9) I enter the address with the city name.

In another cell (C31:F31), I have a drop down list with different city names and when a city is chosen from the drop down list, it displays a certain percentage number in another cell beside it (G31), which is the tax pertaining to that city.

I'm trying to figure out how to get the drop down list cell (C31:F31) to return an item on the drop down list if a certain city text is typed in the address cell (C6:H9).

But I'm having a hard time figuring out how to do so.

player0
  • 124,011
  • 12
  • 67
  • 124
ADOGG4155
  • 35
  • 5

1 Answers1

2

try:

=IFNA(VLOOKUP(VLOOKUP(REGEXEXTRACT(C6, 
 TEXTJOIN("|", 1, 'Tax Rates'!E3:E)), 'Tax Rates'!E3:F, 2, 0), 'Tax Rates'!A3:B, 2, 0))

enter image description here

update:

=IFNA(VLOOKUP(IFERROR(VLOOKUP(REGEXEXTRACT(C6, 
 TEXTJOIN("|", 1, 'Tax Rates'!E3:E)), 'Tax Rates'!E3:F, 2, ), 
 REGEXEXTRACT(C6, TEXTJOIN("|", 1, 'Tax Rates'!A3:A))), 'Tax Rates'!A3:B, 2, ))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • There's no way to include the name of the tax? In the tax rates, certain cities have their own tax which is why if another city (for example) "Hayward" was the City name, it would need to display that specific percentage for that city. I already have the list created in the "Tax Rates" sheet. – ADOGG4155 Apr 08 '22 at 23:05
  • @ADOGG4155 answer and your sheet updated – player0 Apr 08 '22 at 23:21
  • thanks! This is what I wanted to achieve. How can I make it so the cell next to percentage returns the city name based off the city name in C6? I made a new copy to make that edit on: https://docs.google.com/spreadsheets/d/1cH2Oc2sEEKtJ1kgjV9XnZL31Sr0HUZIlu142ZXAolIE/edit?usp=sharing – ADOGG4155 Apr 09 '22 at 00:33
  • @ADOGG4155 delete your G31 cell and use this in C31: `=INDEX(IFNA(VLOOKUP(IFERROR(VLOOKUP(REGEXEXTRACT(C6, TEXTJOIN("|", 1, 'Tax Rates'!E3:E)), 'Tax Rates'!E3:F, 2, ), REGEXEXTRACT(C6, TEXTJOIN("|", 1, 'Tax Rates'!A3:A))), 'Tax Rates'!A3:B, {1, 2,2,2,2}, )))` – player0 Apr 09 '22 at 01:16
  • Hey there, I have another google sheets I'm creating, and I would love your help, is there another way I can reach out to you by chance? – ADOGG4155 Apr 23 '22 at 05:28