I am attempting to convert a model from Excel to Google Sheets that consists of multiple interconnected sheets that lead to a final output used for insurance pricing purposes. I’ve been able to move most of it over; however, the key sticking point is one formula in particular that will not directly translate.

=IF(T2=”NA”,”NA”,IF(NOT(ISNA(MATCH(“Entire State “&S2,INDIRECT(S2),0))),”Entire State “&S2,IF(NOT(ISNA(MATCH(“City of “&D2,INDIRECT(S2),0))),”City of “&D2,IF(ISNA(VLOOKUP(L2,INDIRECT(S2),1,FALSE)),”Remainder of State “&S2,VLOOKUP(L2,INDIRECT(S2),1,FALSE)))))

The above is used at the end of a sheet that consists of a US zip code file that further references two other columns prior to the formula (columns S and T), with S using a series of functions that add an underscore to a state if it has multiple words, while T is used as a helper to reference states/territories 1-52 that are contained in a different sheet.

The formula is intended to produce one of five different possibilities based on a combination of inputs in the zip code file along with another separate table (‘ListLookup’).

‘NA’
‘Entire State’ + S2 (state_w_space)
‘City of’ + D2 (city)
L2 (county_name)
‘Remainder of State’ + S2 (state_w_space)

The formula returns exactly what I need when used in Excel, but will only return one of two outcomes (‘NA’ or ‘Entire State + S2) when used in Google Sheets, which essentially means that every US state (non-territory) comes back as ‘Entire State’ + S2. I’m at a loss, other than seeing that the INDIRECT function might be different in Google Sheets compared to Excel.

Any suggestions regarding how to solve and rewrite for Google Sheets?