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)
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.
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.
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.
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...
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.
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?
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.
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 , 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?
please don't stop creating this content!
I truly appreciate your teaching methods. You are easy to follow and give superb examples.
Thank you, I'm glad you get so much from it. 😁
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)
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.
You deserve more subscribers and we deserve more videos from your channel!
Thank you that is very kind of you to say. Go tell all your colleagues, friends and family to subscribe too 😁
Brilliant! Im all for simplicity!
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.
That is true, they can become slower for large range.
Thank you for this great lecture and your Excellent teaching.
You're welcome. 😁
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.
Very true, there are lots of ways to achieve this that I didn't even cover.
Excellent Mark!
Many thanks!
Mark, sumproduct is still the most direct way for doing ‘dot product’ calculations (aka. inner product). I wouldn’t call it obsolete.
Using SUMPRODUCT for it's original purpose ... you are right, it would still be the easiest.
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...
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.
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?
Why do you left legendary index match or index xmatch ?
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.
Mark, what about the INDEX() (and MATCH()) function? Could we use that to do the lookup, too? You had a video about this!
1:48 I am a little lost… Which video are you referring to? I probably watched it, but I don’t remember. Thanks!
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!
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.
Great! First comment.
First out of the block again. 😁
@@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?