LOOKUP vs SELECT and efficiency

Hi all - just 2 style/clarification questions about overlapping functionality:

LOOKUP() vs. SELECT() ?

INDEX(SELECT( … ), 1) vs ANY(SELECT( … )) when you’re sure there’s only one result, and it needs to be typecast as its type, not as a returned LIST.

Interested in some guidance both in efficiency and best use cases. Any advice appreciated.

Thx!

1 Like
3 Likes

I’d assume ANY(…) and INDEX(…,1) are equivalent in performance, if that is what you’re asking.

2 Likes

Actually, it my impression is that INDEX() is far superior to LOOKUP() when you have a small table or slice (just one or a few records). I recently learned from @MultiTech_Visions about creating a slice of one item (a user table) and then using INDEX(…,1) instead of LOOKUP(). LOOKUP() was causing warnings for my format rules but index does not cause this error. So, I assume that INDEX() is more efficient, at least in regard to format rules.

I thought I might write a really short tip saying “in situations where your table or slice is small, use INDEX() instead of LOOKUP()” but I thought I should first check to see what others have written about this and that brought me to this thread.

P.S. The thread in which I got a very useful tip from MultiTech Visions is here:

3 Likes

Hi Kirk. I’m not sure what point you are trying to make here.

The question here, and my answer, were about comparing INDEX vs ANY. Not about comparing INDEX vs LOOKUP.

INDEX is absolutely a more performant expression than LOOKUP. But they also do two entirely different things though. If you need to lookup a value, you can’t just use INDEX. So I don’t see the point in making any comparison there.

Matt’s advice about using INDEX, or alternatively ANY, on a single-record Slice is definitely a great strategy. You can certainly compare using LOOKUP to the entire strategy, just not to INDEX itself.

Causing errors vs not causing errors is not an indication of efficiency, or the lack of. How was LOOKUP causing errors? Perhaps you weren’t using it quite right?

6 Likes

Agreed

  • They both take the first item out the list, and I imagine they have the same processing time.

There is a difference in certain circumstances though, which make INDEX() a better option to make your “go to” - this way when those certain situations come about you’re covered.

3 Likes

First of all, I need to apologize for misunderstanding your comment and not explaining myself properly. Sorry.

My point was about what @MultiTech_Visions wrote about INDEX() being a better option in some situations. Here’s the situation where I found, thanks to MultiTech_Visions, that INDEX() did the same work as LOOKUP() but without a warning:

I had a table with a single row that I was using for settings and format rules. I used LOOKUP() from another table to determine which records should be shown or hidden and I also used LOOKUP() to access the same settings information for format rules. I didn’t get an error but I did get a warning that my use of LOOKUP() could cause performance issues:

Screen Shot 2021-06-25 at 18.12.37

Then, indeed, I found on my Android device that the performance seemed to be getting worse, though I didn’t notice a problem on my computer. I found a workaround that combined virtual columns and an action that seemed to work so I posted the “tip” I linked to above. Then, I learned from MultiTech_Visions that using INDEX() is a much better solution in my situation.

In a sense, MultiTech_Visions has already given us this lesson in the following tip:

I thought, though, that it might be worthwhile to isolate the point about using INDEX() instead of LOOKUP() when possible, which is one aspect of that tip. I looked for discussion of that specific point and that brought me here. But, I guess there wasn’t a good match between the thrust of this discussion and my point of interest. Once again, sorry for the confusion.

P.S. I think that in most situations the use of INDEX() we are discussing here doesn’t make sense without first making a slice to reduce the data to a single row. In my case, however, I had a table with a single row to begin with so, for me, the lesson was less about using a slice and more about substituting INDEX() for LOOKUP() when a table only has one row (or, perhaps, a very small number of rows).

3 Likes

I prefer IN(..., Current_User[...]) to (... = INDEX(Current_User[...], 1)).

5 Likes

Thanks for the reply. I think I must be slow on the uptake, so to speak, but I’m not sure I understand. Are the IN() and INDEX() expressions you are writing about TRUE/FALSE? If so, that’s different from what I am looking into right now. The INDEX() usage I was referring to is equivalent to LOOKUP() in that one doesn’t need to know what one is looking for, just the location of the cell one wants to check.

1 Like

Steve was simply giving a third option along with ANY() or INDEX(…,1). I don’t believe it was in response to anything you said.

IN() can be useful if you may be dealing with a Slice of more than one record, for example if some user can have more than 1 role.

If you are in fact using a Slice with always only 1 record, I personally would not use IN(), in order to retain better readability of your expression, as using IN() might imply the existence (or the possibility of existence) of more than 1 record in the Slice.

4 Likes

As always, I appreciate your response. I think I understand your point about IN().

I don’t want to be overly persistent in asking about INDEX() in this thread. I was just intrigued by @MultiTech_Visions use of it and was under the impression that a lot of AppSheet creators might not be aware that, in single-record tables/slices, INDEX() can be preferable to LOOKUP().

2 Likes

You know… I’ve never thought about creating multiple “role” records for a user… :thinking:

2 Likes