At present I know of the following ways to get data from one part of a google sheet to another:
VLookup() HLookup()
This is the workhorse of lookups. It should be faster on sorted lookups, but I suspect that it doesn't matter until you get to really big lists. Not robust against column inserts in target block.
Index/match
Less said the better. The syntax is arcane. I suspect that there will be significant extra overhead.
Offset
Similar to Index, but can return a range.
Will depend mostly on how the offsets are calculated.
Query()
Advantage when you want to use something beside the first column to search for, and can return multiple results. Arcane syntax. Inserting a column into the search block target requires hand editing all queries that use that target.
Filter()
Using filter adds an extra step, and an extra sheet. Does it save having to recalculate something multiple times.
GetPivotData
I've never actually used this one. On the face of it, when you need it, there isn't another option that isn't horrendous.
Choose
If you can calculate the index readily, then this may beat nested if in speed, and certain does in terms of messy formula
Importrange()
This works when pulling data from another sheet. I would expect this to be relatively slow. as it has to do a network stat() operation to see when the other file has changed.
Can anyone point to a resource as to which works faster under what circumstances?
Best Answer
VLOOKUP
orMATCH
on sorted arrays will be the fastest among them all,because they use Binary search algorithmImage from Wikimedia (CC4.0 Attribution here)