Thank you for this video - I had been trying for about 3 hours figuring out how to do a matrix value assignment based on an x cordinate and y cordinate and this video solved that setup for me.
Would Xlookup be useful for this problem? I was able to use Xlookup by using Filter for the results range when I used a finite lookup range. I could not get it work, though, if I tried to use an entire row or entire column for the lookup ranges, because then the ranges would be of different sizes.
In google sheet, for this example, your can do double xlookup as below: =XLOOKUP(G13,A2:A5,XLOOKUP(H13,B1:E1,B2:E5)) The inner XLOOKUP will return an entire column of data, than the outer XLOOKUP will return the one cell of that column which matches its lookup value.
@@GM085 Filter function will always return an array of column values (one entire row) from the given ref. range of data. The concept of how your equation works is somehow similar to that double XLOOKUP solution I've provided. The difference is, that filter function do accept more than one condition argument by default, which makes no different in this provided practical example.
@@GM085 Actually, since we already know that the lookup values would be in the 1st row and 1st col in this practice, personally I would prefer something like this: =LAMBDA(TABLE,ROW,COL,XLOOKUP(ROW,INDEX(TABLE,,1),XLOOKUP(COL,INDEX(TABLE,1),TABLE)))(A1:E10,G13,H13) It simplifies the inputs into TABLE, ROW, COL Where TABLE is the entire data range, ROW and COL would be the 2 lookup values for x and y axis.
How would you find the regions instead if there is number under that region (or a check mark), like Jake has 2 region that has numbers, I want the result to show Jake | Midwest | Northeast
why is my XLOOKup not correctly searching for normal accoutn numbers like: 345677 ? When that same number is in the column that my XLOOKUP is looking at, but my XLOOKUP is saying that is "Not found" based on my criteria, but again it should come back as a match but XLOOKUP is not matching it?
Hi - I’m having trouble linking a specific cell (or range of cells) from one workbook to another. Creating the link from the source workbook and inserting it onto the second workbook isn’t the problem. The problem is, after updating information on the source workbook and adding rows, the linked cell is no longer correct. Specifically, I have a directory of school districts listed in a Google Sheets workbook. And I have a separate workbook of construction projects happening in those individual school districts. I’ve created a link to those specific School Districts on the Projects workbook, so that each Project references a corresponding School District the work is being performed in. And likewise, each School District lists the Projects. However, after updating the School District workbook with each individual Project by adding a row, all the linked School Districts referenced in the Projects workbook are wrong because the destination has changed. I’ve watched dozens of TH-cam videos and your collection has helped me with numerous issues and challenges. In particular, conditional formatting with a formula you showed me how to create. But with this problem I’m not quite sure where to begin. Please help. Thanks in advance. -Chris @ExcelGoogleSheets
Keep up the great work!
Probably the best Google Sheets teacher on the internet.
Thx!
I am crying happy tears. Thank you so much for solving my matrix formula woes!
Thank you for this video - I had been trying for about 3 hours figuring out how to do a matrix value assignment based on an x cordinate and y cordinate and this video solved that setup for me.
It's good to learn about the Xmatch! Thanks for the great content!
He 's true master!..Great teacher.
Thanks for the great value 👍
Excellent as always!
Thank you!
Thank you!
Your video make me famous in my Job😂, thanks 🙏
What is your opinion about using DGET function instead of the above two to solve the above problem.
DGET is fine when you don't have much data, but gets really slow with lots of data.
Would Xlookup be useful for this problem? I was able to use Xlookup by using Filter for the results range when I used a finite lookup range. I could not get it work, though, if I tried to use an entire row or entire column for the lookup ranges, because then the ranges would be of different sizes.
Could I solve this problem with XLOOKUP? Yes.
Would I? No. It would be too weird and convoluted.
In google sheet, for this example, your can do double xlookup as below:
=XLOOKUP(G13,A2:A5,XLOOKUP(H13,B1:E1,B2:E5))
The inner XLOOKUP will return an entire column of data, than the outer XLOOKUP will return the one cell of that column which matches its lookup value.
@@ExcelGoogleSheets This is how I did it with Xlookup and Filter: =xlookup(G13,A1:E1,filter(A2:E10,A2:A10=H13))
@@GM085
Filter function will always return an array of column values (one entire row) from the given ref. range of data.
The concept of how your equation works is somehow similar to that double XLOOKUP solution I've provided.
The difference is, that filter function do accept more than one condition argument by default, which makes no different in this provided practical example.
@@GM085
Actually, since we already know that the lookup values would be in the 1st row and 1st col in this practice,
personally I would prefer something like this:
=LAMBDA(TABLE,ROW,COL,XLOOKUP(ROW,INDEX(TABLE,,1),XLOOKUP(COL,INDEX(TABLE,1),TABLE)))(A1:E10,G13,H13)
It simplifies the inputs into TABLE, ROW, COL
Where TABLE is the entire data range, ROW and COL would be the 2 lookup values for x and y axis.
thanks! make one for MMULT please!
Why do you need MMULT?
wich one is better speed to find at large data? vlookup or indexXmatch? coz i need to combine with an arrayformula
I don't think there is any difference, but I would probably use VLOOKUP for arrayfromula.
@@ExcelGoogleSheets thanks sir! yes vlookup work very well
How would you find the regions instead if there is number under that region (or a check mark), like Jake has 2 region that has numbers, I want the result to show Jake | Midwest | Northeast
Make video on all finance function pl.
th-cam.com/users/LearnGoogleSpreadsheetssearch?query=finance
@@ExcelGoogleSheets didn't get playlist of finance function
Please share data file for practice
I have 200 Sheets about employee performance, how to make it just 1 sheet? Please help me.
th-cam.com/users/LearnGoogleSpreadsheetssearch?query=combine
why is my XLOOKup not correctly searching for normal accoutn numbers like: 345677 ? When that same number is in the column that my XLOOKUP is looking at, but my XLOOKUP is saying that is "Not found" based on my criteria, but again it should come back as a match but XLOOKUP is not matching it?
The answer is in this video th-cam.com/video/kt8nUzyKj-A/w-d-xo.html
Hi - I’m having trouble linking a specific cell (or range of cells) from one workbook to another. Creating the link from the source workbook and inserting it onto the second workbook isn’t the problem. The problem is, after updating information on the source workbook and adding rows, the linked cell is no longer correct.
Specifically, I have a directory of school districts listed in a Google Sheets workbook. And I have a separate workbook of construction projects happening in those individual school districts.
I’ve created a link to those specific School Districts on the Projects workbook, so that each Project references a corresponding School District the work is being performed in. And likewise, each School District lists the Projects.
However, after updating the School District workbook with each individual Project by adding a row, all the linked School Districts referenced in the Projects workbook are wrong because the destination has changed.
I’ve watched dozens of TH-cam videos and your collection has helped me with numerous issues and challenges. In particular, conditional formatting with a formula you showed me how to create. But with this problem I’m not quite sure where to begin.
Please help. Thanks in advance. -Chris @ExcelGoogleSheets