TURN VLOOKUP INTO INDIRECT VLOOKUP

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
159
Office Version
  1. 365
Platform
  1. Windows
Hi, I've tried and tried and it just doesn't work. I'm certain that what I need is extremely simple. Please help me adjust my existing formula.
I will show you one formula that works great, and the other will be the one that I need to tweak.

I need the second formula to work even though the tab name (example: Thursday120320) does not exist. ISREF as well please.
FYI - Sometimes the tab that the VLOOKUP is looking for exists or does not exist because there was no labor performed on that day. The first formula works even if tab "Thursday120320" does not exist within the workbook.

Here is my excellent formula as a reference:
Excel Formula:
=IF(ISREF(INDIRECT("'"&K$8&"'!$A1")),IF(ISNA(VLOOKUP("*"&$C9&"*",INDIRECT("'"&K$8&"'!m6:m3056"),1,0)=FALSE),"Off/Vacation",$C9),"--")

Here is my formula that I need to tweak in one column to the right of the 1st formula:
Excel Formula:
=IF(OR(K9="Off/Vacation",K9="--"),"--",
VLOOKUP("*"&$C9&"*",Thursday120320!$A:$B,2,0))


Thank so much, Juicy!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't the 2nd formula work?
 

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
159
Office Version
  1. 365
Platform
  1. Windows
Hello Fluff, thank you for responding. I tried to install the XLLB thing, but my company computer security system blocked it. I'm sorry.

I should let you know that this is a monthly spreadsheet that will eventually have 30 or 31 tabs. The tab names will change every month.
For example January tabs will be named like this: Monday011121, Tuesday011221, Wednesday011321, Thursday011421, and so on to the last day of that month.

The 2nd formula in my December file DOES work if the tab named Thursday120320 exists or not. (I'm only giving you this one date but there are 31 columns that will have this formula)
The problem is when it is a new month and the past dated tabs (December tabs) are deleted from the workbook - and then the current month daily tabs are inserted into the workbook.

The 2nd formula will give me back a "--" if an employee name is not found because it actually is still looking for a tab named Thursday120320.

The 1st formula actually looks for a tab that has the same name as the column header - See the K$8 portion of the formula. K8 is the header name of the column. I've circled it in red text for you on the images uploaded.
Excel Formula:
=IF(ISREF(INDIRECT("'"&[COLOR=rgb(184, 49, 47)]K$8[/COLOR]&"'!$A1")),IF(ISNA(VLOOKUP("*"&$C9&"*",INDIRECT("'"&[COLOR=rgb(184, 49, 47)]K$8[/COLOR]&"'!m6:m3056"),1,0)=FALSE),"Off/Vacation",$C9),"--")

Is it possible for my 2nd formula to look at the same header name (K8) AND look for a tab with the same name while keeping the second part of the VLOOKUP intact?
Meaning keeping the $A:$B,2,0 portion as it is.

Excel Formula:
=IF(OR(K9="Off/Vacation",K9="--"),"--", VLOOKUP("*"&$C9&"*",Thursday120320![COLOR=rgb(184, 49, 47)]$A:$B,2,0[/COLOR]))
 

Attachments

  • Tab Name for Vlookup_Dec.PNG
    Tab Name for Vlookup_Dec.PNG
    19.6 KB · Views: 2
  • Tab Name for Vlookup_Jan.PNG
    Tab Name for Vlookup_Jan.PNG
    20.1 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
You can use indirect on it like
Excel Formula:
=IF(OR(K9="Off/Vacation",K9="--"),"--",VLOOKUP("*"&$C9&"*",INDIRECT(K8&"!$A:$B"),2,0))
 
Solution

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
159
Office Version
  1. 365
Platform
  1. Windows
SOLVED, of course......It just took adding "INDIRECT(K8&" ? I knew it would be simple, but not THAT simple. 🤨
Sorry for gushing over you...….I thank you. I learn everything you've shown me on all my posts for future projects. Thank you!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,637
Messages
5,719,962
Members
422,251
Latest member
bonebreaker100

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top