Excel Multiple Column Lookups: Which Method is Best?

[@ttilta]

I appreciate all of the excel youtubers I've come across. But your channel is so different, you open my mind to approaches I wouldn't even think of, and often times I end up using similar methods in my daily life.

[@spy-py]

please don't stop creating this content!

[@CAKPRashique]

Your teaching style is amazing , i always waiting for ur new videos , and i never miss any video

[@mrpage221]

I truly appreciate your teaching methods. You are easy to follow and give superb examples.

[@ExcelOffTheGrid]

Thank you, I'm glad you get so much from it. 😁

[@karolinab9749]

Thank you! 😊 I appreciate you go an extra mile and compare different functions. I agree your videos are mind openers and motivating to look for solutions fitting the context.

[@LaloinLondon]

You deserve more subscribers and we deserve more videos from your channel!

[@ExcelOffTheGrid]

Thank you that is very kind of you to say. Go tell all your colleagues, friends and family to subscribe too 😁

[@Al-Ahdal]

@ExcelOffTheGrid , your contents are awesome.

[@huseyinburaktasci]

An inspiring teaching method! Thank you! I will consider changing my Sumproduct formulas to Xlookup version.😅

[@giorgioberardi3940]

Great contents, Mark, and spectacularly explained. Loved the overview grid of pros and cons for each function analysed. Bravo!

[@calvinsweet3400]

Brilliant! Im all for simplicity!

[@Excelambda]

Great video!! Alternative with entire arrays: =XLOOKUP(BYROW(G4:H7&CHAR(7),CONCAT),BYROW(B4:C19&CHAR(7),CONCAT),E4:E19) - never found a CHAR(7) in any data => no risk to use it as join char ( there are other rare UNICHARs) - byrow has versatility to work with "n" columns arrays. (if not next to each other HSTACk will do)

[@ExcelOffTheGrid]

Very nice BYROW ✅ As you say, there are loads of characters which I've never seen in the wild either. Also, those character don't just have to exist in the dataset, they also have to exist in the exactly the right positions within a dataset which could then lead to duplicate values.

[@FarshidMaleki]

Great video. Thanks

[@jerrydellasala7643]

Very nice!

[@IvanCortinas_ES]

Excellent analysis. Many possibilities for doing the same thing. In the end, each person decides which solution is best for them. Thanks for the presentation.

[@ExcelOffTheGrid]

Very true, there are lots of ways to achieve this that I didn't even cover.

[@ninali5241]

Great video!

[@adin6429]

Why do you left legendary index match or index xmatch ?

[@ExcelOffTheGrid]

XMATCH (spacer) and XMATCH (boolean) techniques would work almost exactly the same as XLOOKUP. Therefore, I didn't think it was worth a separate mention. MATCH would be similar to XMATCH, but with the already known differences.

[@alamiralshafay]

Thank you for this great lecture and your Excellent teaching.

[@ExcelOffTheGrid]

You're welcome. 😁

[@chrism9037]

Excellent Mark!

[@ExcelOffTheGrid]

Many thanks!

[@josh_excel]

For the XLOOKUP Spacer solution, each range will be recopied into memory for each lookup and that will slow things down for large ranges. In those cases, I make a helper column with the combined values.

[@ExcelOffTheGrid]

That is true, they can become slower for large range.

[@GeertDelmulle]

Mark, sumproduct is still the most direct way for doing ‘dot product’ calculations (aka. inner product). I wouldn’t call it obsolete.

[@ExcelOffTheGrid]

Using SUMPRODUCT for it's original purpose ... you are right, it would still be the easiest.

[@ahmed007Jaber]

Thank u Mark I learnt couple of tricks Ever used index and match? Which was is the most efficent id i have 90k rows and many columns that have formulas?

[@mohammedelsakally540]

Thank you Mark for your always valuable content you are always providing to your followers, but by practicing Xlookup (Spacer) with a huge range of data will be slower in calculation and the user will need helper column to concate both lookup value and lookup array so I think Xlookup (Boolean) will be more effecient.. finally, the comparisons are awesome, and learning us the differences between each method...

[@ExcelOffTheGrid]

Have you done any testing on the spacer version vs the boolean version? I've never tested it. I've always assumed it would be similar, but happy to learn more.

[@JJ_TheGreat]

1:48 I am a little lost… Which video are you referring to? I probably watched it, but I don’t remember. Thanks!

[@ExcelOffTheGrid]

This video: Excel's dirty little secrets - 5 things it does you don't expect! kzbin.info/www/bejne/iHSUqoZ6gph_l80

[@mr.bachubhaibhigona9090]

Can you please tell me if it is possible to have a recursive Lambda function in Excel where the counter used in IF condition is updated based on a condition e.g. =LAMBDA(input, IF(input0,myfunction(do something, IF(condition is met, input-1,input)), output) If it is not possible to use above logic using LAMBDA, please let me know if there is any other way to do this.

[@ExcelOffTheGrid]

Yes, it is possible. In your example the function called is “myfunction”; therefore, you would need to save your LAMBDA in the name manager with the same name of “myfunction”. That will give you a recursive LAMBDA.

[@JJ_TheGreat]

10:16 What does the MAP() function do - and why is it needed? If you have a video on this, could you please refer me to it?! Thanks!

[@tinhoyhu]

Map takes one or more ranges and applies them cell by cell to the function in the last argument. The ranges all have to be the same dimension (n x m), and the output will return with the same dimensions. Note that the function doesn't have to be lambda. =map(a1:a5, b11:b15, {1;3;5;7;9} , average) Would return a 5x1 array of the average of each of the 5 values in the three ranges.

[@ExcelOffTheGrid]

Good summary 👍

[@mehmedbeyim]

Sir, your videos are so beneficial, especially for sales guys. i have an inquiry. i download some reports from a customer website. i am done with the formula but each time i have to open these reports after opening my excel sheet. is there a way to do it without opening the customer reports? i'd like to embed these customer files to my excel sheet. Whenever i need the updated reports, i would like to just refresh my excel sheet and all updated figures should come up. please send me the link if you make a video about that. Thanks in advance

[@TP014563]

Excellent! Yes! Please tell the people man!! What people fail to understand is that in many cases you will end up tweaking/adjusting your formula based on your use case. Meaning; in some scenarios FILTER works better than an XLookup and vise versa. It all depends one what type of search & result you are looking for. Sometimes I end up using both functions and others in the same sheet to display different types of information. One question though, I am interested in the Custom Formats that you have there in the ribbon. looks very neat!. How did you create this? Is it an add-in or used excel to do it?

[@roboats5685]

I really enjoy your videos where you compare & contrast various functions (older and newer) to accomplish a given task. Sometimes it seems like there are 10 ways to do the same thing in Excel, which can be overwhelming, and I appreciate the explanation of the pros and cons of the approaches. Side note: I secretly enjoy your "incorrect" pronunciation of "H" (haitch!), so I was mildly disappointed to hear the correct pronunciation in this video. 😂

[@ExcelOffTheGrid]

Yes, there are lots of ways to achieve the same thing. Though most of what I cover fits into our various blueprints, so I certainly don’t cover all 10, just the ones which fit into other larger solutions. It appears my pronunciation of (h)aitch was annoying too many people. So now, I say Aitch on KZbin, and stick with my colloquial Haitch in real life. 🤣

[@ahmad.syawal10]

how about multiple lookup for column and row, which method is better

[@JJ_TheGreat]

Mark, what about the INDEX() (and MATCH()) function? Could we use that to do the lookup, too? You had a video about this!

[@ExcelOffTheGrid]

Yes, you definitely could use INDEX/MATCH, it would be similar to the XLOOKUP options.

[@crabby786]

what do you mean with "scalar"?

[@teoxengineer]

Mark hi. It is very useful and handy tutorial like Univercity lectures. This type of testing result studies are more efficient way to analyse excel functions instead of learning just its simple ways. So cleaver and attractive

[@geoffroyc]

Great video, super informative! I would have been tempted just to go with a simple boolean logic using INDEX, many options available, but something like =INDEX(E:E;MAX(($B$4:$B$19=G4)*($C$4:$C$19=H4)*ROW($4:$19))), which doesn't have the first disadvantage of the "-" of the XLOOKUP, but not the advantage of the spill.

[@ExcelOffTheGrid]

Full column and row references, I definitely wouldn't go there, unless I was backed into a corner. The risk of calculating the wrong wrong result is just way too high.

[@geoffroyc]

@@ExcelOffTheGrid Agree again! I would definitely use table references normally, but still XLOOKUP is a much better option.

[@Al-Ahdal]

Great! First comment.

[@ExcelOffTheGrid]

First out of the block again. 😁

[@Al-Ahdal]

@@ExcelOffTheGrid , your videos are very informative and great content indeed. Is your plan for python in excel and complex array formulas, and Power BI in future for EOTG?

[@thinktoomuchb4028]

Actually, I'd choose the method from your How to Spill Multiple FILTER Functions video (1 year ago), which uses FILTER with TEXTJOIN in a Table.

[@LaloinLondon]

What about a video showing how LAMBDA function can replace the volatile function OFFSET?

[@ExcelOffTheGrid]

I’ve not tried, but I don’t think that is possible. Because of the way the calculation chain works, OFFSET or an equivalent needs to be volatile.

[@LaloinLondon]

@ExcelOffTheGrid The finance department is running an a big shared file that contains around 5000 cells with the OFFSET function. I need to replace them with non-volatile functions. I tried with INDEX and it worked(just one cell). But I was wondering if LAMBDA could be an option as it also is a reference to a cell address (same as the INDEX function)

[@ExcelOffTheGrid]

LAMBDA doesn't do anything itself - you place other functions inside a LAMBDA. If INDEX works, then use that, it will be much better. Then you could place that INDEX in a LAMBDA to create a more re-usable solution. INDEX has similar, but not identical, functionality to OFFSET. So it depends on how they are using OFFSET which will determine if it can be replaced by INDEX.

[@LambdaBam]

SUMPRODUCT is still useful to sum the product of two arrays. There is no other single function that can do that.

[@ExcelOffTheGrid]

SUM is a single function which can do that. But the syntax is not quite as easy.