I find the syntax of index match convoluted, and difficult to read. (And in general I dislike nested functions. What workarounds are there when the lookup column is on the right of the answer column?

  • Good idea to document here your solutions. Thank you. I think that the situation describen in the answer should be included in the question, including a demo spreadsheet. Also I think that the question should be more specific like including if you are looking for built-in functions only, if it's important or not that this will be compatible with other spreadsheets because you plan to export to one of them, etc. – Rubén Jan 9 '20 at 15:04
  • What do you think of QUERY? What about custom functions? – Rubén Jan 9 '20 at 15:07

I had the following situation:

Screenshot source data

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:


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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.