Google-sheets – How to avoid INDEX MATCH on google sheets

I had the following situation:

I had the botanical name. I wanted the common name.

The shot above is from a sheet labeled Trees, so I defined two more Named Ranges as TCommon which was column E3 to end, and TBotanical which was column F3 to end.

(By using a prefix from the sheet name, all of the named ranges for that sheet are together in the NamedRange panel.)

Now Google Sheets allows the creation of arrays on the fly. So I can use the syntax of VLOOKUP, which to me is more straight forward:

=VLOOKUP(M4,{TBotanical,TCommon},2,FALSE())

Now I didn't have to define named ranges for this. I could have done it like this:

=VLOOKUP(M4,{Trees!F3:F1000,Trees!E3:E1000},2,FALSE())

The named ranges help me understand next year when I have to hack this again for my suppliers new format.

But named ranges give me another win: They adjust if you move them. I can insert a column between E and F.

The downside of Named Ranges: You can't really define them using E3:E syntax. Oh, you can define them that way, but they fill in the rest of the column part with the current last row. This has occasionally bitten me.

Array syntax in Google Sheets works like this:

{} enclose the array.

Commas separate elements in the same column.

Semicolons start a new column.

A comment asked my thoughts on query & custom functions.

Query has some awesome properties. The problem with it: the full query runs anytime any of the source data changes.

It uses an a different syntax for the query itself, with extensive escaping and quoting. It does not adjust gracefully to changes in data structure of the source.

Custom functions require learning a new language. For some things they are the only way. My understanding is that a custom function adds another layer of parsing and interpretation so that a custom function equivalent to a built-in function runs MUCH more slowly.