Need Help with Indirect/Match formula

odonovanc

New Member
Joined
Oct 4, 2017
Messages
48
I have a workbook with many tabs. One set of tabs are years, 2020, 2021, etc. Then I also have tabs for each month of the years so Mar 2021, Apr 2021, etc.

I am trying to create a lookup where a formula will look up the correct month tab, in this case Mar 2021. Here is my formula and where I am:

=-IFERROR((IFERROR(VLOOKUP($C64,INDIRECT(M$62&"!$C$5:$T$50000"),MATCH(M$61,INDIRECT(M$62&"!$C$4:$T$4"),0),0),0)),"")

Some details: in my current sheet, M61 is the month "Mar" and M62 is the Year "2021". I need my formula to lookup C64 from my current sheet to the Mar 2021 tab and match the month found in cell M61 to row 4 of the Mar 2021 tab.

My formula is pulling in the amounts from just the "2021" tab and not the Mar 2021 tab like I am trying to do. Everything is pulling correctly, it is just pulling from the wrong sheet. How can I tweak this to get it to pull from Mar 2021 tab instead of just 2021?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
This part of your formula is telling VLOOKUP to look only at the 2021 sheet.
Excel Formula:
INDIRECT(M$62&"!$C$5:$T$50000")
If you want this to refer to the Month-Year tab, then:
Excel Formula:
INDIRECT("'"&M$61&" "&M$62&"'!$C$5:$T$50000")
Note the character nested between those double quotes is a single quote " ' " without the spaces...and the 2nd occurrence is " ' ! again without spaces.
That forms the string 'Mar 2021'!$C$5:$T$50000. Your other INDIRECT formula may need attention too.
 

odonovanc

New Member
Joined
Oct 4, 2017
Messages
48
This part of your formula is telling VLOOKUP to look only at the 2021 sheet.
Excel Formula:
INDIRECT(M$62&"!$C$5:$T$50000")
If you want this to refer to the Month-Year tab, then:
Excel Formula:
INDIRECT("'"&M$61&" "&M$62&"'!$C$5:$T$50000")
Note the character nested between those double quotes is a single quote " ' " without the spaces...and the 2nd occurrence is " ' ! again without spaces.
That forms the string 'Mar 2021'!$C$5:$T$50000. Your other INDIRECT formula may need attention too.
This is perfect. Thanks so much.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
Great!...glad to help.
 

Forum statistics

Threads
1,144,680
Messages
5,725,751
Members
422,636
Latest member
GatorsBucs

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