Speed important: Is relying on a single INDEX lookup from a huge table faster than 'building' the desired output? (image example within)

[d0rian]

(FYI: my example table of data is just for illustrative purposes...the actual data table is between 4,000 - 6,000 rows of data, and my list of on-sale items is about 400, not just the 4 you see in the image, so it might seem like a trivial speed difference here, but could very well be significant at scale. My current problem is that my excel workbook is pulling in tick-by-tick stock updates and is very unstable, crashing frequently, so I'm looking to create the lowest-resource formulas possible...)

I need to build the [fruit]_[color]_[cost]_[qty] text string as shown in the image below for a small subset of items from a massive list. I'm currently using the method on the LEFT whereby I just paste the huge data table (which already includes a column containing the desired text string that I've created in a different workbook and pasted as hardcoded values here), and then run an INDEX lookup for the 4 on-sale items. But I want to know if there'd be a material difference in speed/performance if I used the method on the RIGHT, whereby I hard-code the Fruit/Color/Cost/Qty, and then BUILD the desired text-string output by simply concatenating the items with an _underscore_ in between them.

My current method (LEFT side) relies simply on an INDEX lookup of hardcoded values...but as explained above, the data_table I'm working with has 5,000+ rows, and there's ~200 'on-sale' items, not 4...I just don't know "how long" it takes Excel to search through a huge data table with its INDEX function, or whether simply 'building' my desired text-strings from hard-coded color/cost/qty values will be faster / less resource-intensive.

 

[Jonmo1]

I would think the one on the left is more efficient

Method on the right requires 3 index/match functions, 1 to pull Color, 1 for cost, 1 for quantity
Then those are concatenated together

Method on the left does the concatenation on the lookup table, and only 1 index/match function is required to pull the result.

Concatenate functions are far more efficient than index/match.
It's not a performance hit to have that concatenation on the lookup table.
But 3 index/match vs 1 index/match is a performance hit.

 

[d0rian]

I would think the one on the left is more efficient

Method on the right requires 3 index/match functions, 1 to pull Color, 1 for cost, 1 for quantity
Then those are concatenated together

Method on the left does the concatenation on the lookup table, and only 1 index/match function is required to pull the result.

Concatenate functions are far more efficient than index/match.
It's not a performance hit to have that concatenation on the lookup table.
But 3 index/match vs 1 index/match is a performance hit.

Thanks for reply -- reading my OP, I realize that I wasn't as clear as I thought about something: in the RIGHT-side image, that 4x3 shaded grid is actually NOT pulling the Color/Cost/Qty values using an INDEX lookup. (That's what I tried to explain in the "have a macro..." cell in the upper-right of the image.) I'm actually hard-coding those Color/Cost/Qty values by running a macro that pulls them from the huge data table. Otherwise, you'd be completely correct in that 3 INDEX/MATCH functions + 1 Concatenating function would clearly be slower than a single INDEX/MATCH function. But There's actually *no* INDEX/MATCH'ing going on on the right-hand side at all. There's ONLY the 1 Concatenating function of values that are hardcoded. So that's really the speed-comparison at issue...do I want to run 400 INDEX/MATCH formulas (the LEFT side method) that each have to search a 5,000+ row table? Or would it be faster to instead "build" those 400 text strings with CONCATENATE formulas (using no INDEX/MATCH'ing or searching of the huge data table...?)

Hope that was clearer!