XLOOKUP or VLOOKUP Based on Sheet Tabe Name!?! Excel Magic Trick 1833
Вставка
- Опубліковано 6 лип 2024
- Download Excel File: excelisfun.net/files/EMT1833-...
Learn how to use the worksheet tab name as the lookup value in XLOOKUP or VLOOKUP..
Topics:
1. (00:00) Introduction
2. (00:24) CELL Function
3. (01:21) TEXTAFTER Function
4. (01:49) RELPACE and SEARCH functions.
5. (02:22) XLOOKUP or VLOOKUP
6. (03:27) Copy Lookup Formula
7. (04:13) Summary
8. (04:26) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula
I love this Magic trick to get the tab name. Awesome, thanks Mike.
Thanks for the love, Matt!!!!
Excellent Mike, very clever!
Thanks, Chris M!!!!
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher!!!!
Excellent
Glad you like it : )
Thanks Mike :)
You are welcome, Formula Guy John!!!
Great Trick ❤
Glad you like it!!!!
You have some great videos and thank you for sharing. I have an excel sheet that I'm sharing via google to my foreman for entering time on there tablets. I have two columns, one is "Type" (Shift Worked) that is a drop down for ST, OT, 2ND, 3RD and the other column is a manual enter for numbers (hours worked). I'm looking for a single line formula for ST, OT, etc. that will grab the correct "Type (ST, OT, etc.)" & grab the hours plus the sum for the week for that "type".
Example:
24-ST
8-OT
8-2nd
Good but I've not actually had the need to lookup sheet names. I do have lookup Tables on different sheets. If the columns are matching I would reposition them (if necessary) and use 3D references to VSTACK (or with only 3 lookup Tables I would just enter them separately in VSTACK)?? Or am I missing something in this setup?
I guess it depends on how many lookup tables on sheets you have. It's usually not a good way to design things if you can avoid it. Every time you get a new sheet with a lookup table, you have to adjust any formulae to accomodate, which has the potential to lead to error. VSTACK works, it's just not dynamic for when you add more sheets. A single lookup table (the first column could be the equivalent of the sheet name) is always best if you can accomodate that. If you're only ever going to have X number of lookup tables, and have a definite need to separate onto different tabs, then the VSTACK approach is fine - if a little ugly!
@ricos1497 yes bit the solution for an evolutionary number of sheets is to set them up as 3D references then the expansion can be dynamic.
You can't make adjustments to Excel Tables with multiple sheets selected. I tried that but always got an error.
@@richardhay645 or not to have ever expanding numbers of sheets! I don't think I've ever seen a valid use of that method in Excel and remain to be convinced! Especially with things like power query and pivot tables.
They need to just add "tab" as a definer within CELL, would make formula building a lot easier. I currently use MID to get to the [ within each filename.
That would be cool : )
Great hint. One tiny correction -I think not quite available in all versions - Excel for Web supports CELL() but not “filename” among others. Any/all Desktop versions perhaps.
O, yah. Sorry. I don't even consider Online Excel a real version because it is missing so many things. But I guess that makes since that it does not have filetype. Thanks for the tip : )
It seems that i am not using the right office. I am using Microsoft Office Professional Plus 2016 and i am getting errors😭
You will need to use VLOOKUP rather than XLOOKUP. The CELL part works fine : )
The workbook download link is incorrect. .🤗
I am sorry about this. I forgot to upload the file and the file is in a location that I cannot access right now. I will have to post the workbook file in about 4 hours.
It should be working now.
@@excelisfun Thank you for your attention. 🤗
@@JoseAntonioMorato You are welcome!