0

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?

4
  • 2
    Sorted vlookup does binary search, that's log(N) operations versus N operations for straightforward search. The difference is noticeable already on moderate size arrays. Care must be taken if one requires exact match (which is usually the case); "double vlookup" is the method to use then. – user135384 Apr 5 '18 at 16:33
  • The mention of importrange next to filter is confusing; other commands could be used with importrange too. Why specifically attach it to filter? – user135384 Apr 5 '18 at 16:34
  • Fixed the importrange mention. Thanks. – Sherwood Botsford May 6 '18 at 21:58
  • Would the individual who downchecked this question kindly explain why? – Sherwood Botsford May 6 '18 at 21:59
2

VLOOKUP or MATCH on sorted arrays will be the fastest among them all,because they use Binary search algorithm

Image from Wikimedia (CC4.0 Attribution here)

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.