XLOOKUP or VLOOKUP Based on Sheet Tabe Name!?! Excel Magic Trick 1833

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

[@mattschoular8844]

I love this Magic trick to get the tab name. Awesome, thanks Mike.

[@excelisfun]

Thanks for the love, Matt!!!!

[@chrism9037]

Excellent Mike, very clever!

[@excelisfun]

Thanks, Chris M!!!!

[@SyedMuzammilMahasanShahi]

Thanks Mike for this EXCELlent video.

[@excelisfun]

You are welcome, Fellow Teacher!!!!

[@simfinso858]

Excellent

[@excelisfun]

Glad you like it : )

[@johnborg5419]

Thanks Mike :)

[@excelisfun]

You are welcome, Formula Guy John!!!

[@shubhampawar8506]

Great Trick ❤

[@excelisfun]

Glad you like it!!!!

[@newageelectricllc5098]

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

[@richardhay645]

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?

[@ricos1497]

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!

[@richardhay645]

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

[@excelisfun]

You can't make adjustments to Excel Tables with multiple sheets selected. I tried that but always got an error.

[@ricos1497]

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

[@Darkslide820]

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.

[@excelisfun]

That would be cool : )

[@davismartin2537]

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.

[@excelisfun]

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 : )

[@abdiazizibrahim9163]

It seems that i am not using the right office. I am using Microsoft Office Professional Plus 2016 and i am getting errors😭

[@excelisfun]

You will need to use VLOOKUP rather than XLOOKUP. The CELL part works fine : )

[@JoseAntonioMorato]

The workbook download link is incorrect. .🤗

[@excelisfun]

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.

[@excelisfun]

It should be working now.

[@JoseAntonioMorato]

@@excelisfun Thank you for your attention. 🤗

[@excelisfun]

@@JoseAntonioMorato You are welcome!