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.
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.
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. 😂
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 TH-cam, and stick with my colloquial Haitch in real life. 🤣
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?
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.
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. 😁
please don't stop creating this content!
@ExcelOffTheGrid , your contents are awesome.
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!
Great video!
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.
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 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.
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.
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. 😂
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 TH-cam, and stick with my colloquial Haitch in real life. 🤣
Mark, what about the INDEX() (and MATCH()) function? Could we use that to do the lookup, too? You had a video about this!
Yes, you definitely could use INDEX/MATCH, it would be similar to the XLOOKUP options.
1:48 I am a little lost… Which video are you referring to? I probably watched it, but I don’t remember. Thanks!
This video: Excel's dirty little secrets - 5 things it does you don't expect!
th-cam.com/video/RC1ySDM3Lak/w-d-xo.html
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.
Good summary 👍
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?