'If cell contains a certain text, return a specific drop down list item (Google Sheets)

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.

regexgoogle-sheetsgoogle-sheets-formulavlookuptextjoin


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions