2 Way Lookup, INDEX MATCH, VLOOKUP - Excel & Google Sheets

No video

2 Way Lookup, INDEX MATCH, VLOOKUP - Excel & Google Sheets

Рет қаралды 10,439

Learn Google Sheets & Excel Spreadsheets

Күн бұрын

2 Way Lookup, INDEX MATCH, VLOOKUP - Excel Google Sheets.
#indexmatch #excel #googlesheets

Пікірлер: 32

@OmriLevy Жыл бұрын

Keep up the great work! Probably the best Google Sheets teacher on the internet. Thx!

@stellar_debriss 3 ай бұрын

I am crying happy tears. Thank you so much for solving my matrix formula woes!

@syukraakhmad3822 Жыл бұрын

Your video make me famous in my Job😂, thanks 🙏

@General_Cap Жыл бұрын

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.

@lpanebr Жыл бұрын

It's good to learn about the Xmatch! Thanks for the great content!

@mercyfrs Жыл бұрын

He 's true master!..Great teacher.

@AndrewForte1 Жыл бұрын

Thanks for the great value 👍

@lauraa.3593 Жыл бұрын

Excellent as always! Thank you!

@ExcelGoogleSheets Жыл бұрын

Thank you!

@Dark_side9999 10 ай бұрын

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

@Mohammedbasim Жыл бұрын

What is your opinion about using DGET function instead of the above two to solve the above problem.

@ExcelGoogleSheets Жыл бұрын

DGET is fine when you don't have much data, but gets really slow with lots of data.

@TheMasterfulMishaps Жыл бұрын

thanks! make one for MMULT please!

@ExcelGoogleSheets Жыл бұрын

Why do you need MMULT?

@alizaenalabidinassegaf4493 Жыл бұрын

wich one is better speed to find at large data? vlookup or indexXmatch? coz i need to combine with an arrayformula

@ExcelGoogleSheets Жыл бұрын

I don't think there is any difference, but I would probably use VLOOKUP for arrayfromula.

@alizaenalabidinassegaf4493 Жыл бұрын

@@ExcelGoogleSheets thanks sir! yes vlookup work very well

@GM085 Жыл бұрын

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.

@ExcelGoogleSheets Жыл бұрын

Could I solve this problem with XLOOKUP? Yes. Would I? No. It would be too weird and convoluted.

@bloodyping9644 Жыл бұрын

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 Жыл бұрын

@@ExcelGoogleSheets This is how I did it with Xlookup and Filter: =xlookup(G13,A1:E1,filter(A2:E10,A2:A10=H13))

@bloodyping9644 Жыл бұрын

@@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.

@bloodyping9644 Жыл бұрын

@@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.

@syukraakhmad3822 Жыл бұрын

I have 200 Sheets about employee performance, how to make it just 1 sheet? Please help me.

@ExcelGoogleSheets Жыл бұрын

kzfaq.infosearch?query=combine

@latest4323 Жыл бұрын

Make video on all finance function pl.

@ExcelGoogleSheets Жыл бұрын

kzfaq.infosearch?query=finance

@latest4323 Жыл бұрын

@@ExcelGoogleSheets didn't get playlist of finance function

@usmaniqbal1836 Жыл бұрын

Please share data file for practice

@ignacioa3698 Жыл бұрын

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?

@ExcelGoogleSheets Жыл бұрын

The answer is in this video kzfaq.info/get/bejne/odpoobir3rHOXXU.html

@Shotglass1362 Жыл бұрын

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 KZfaq 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