Indirect vlookup match

tanvirabid

New Member
Joined
Aug 21, 2019
Messages
8
I have two sheets for 2 assets: 13100 and 13200. I have monthly numbers (i.e. Jan, Feb etc) for several accounts. I am trying to create a formula to get the numbers for a particular month for a particular asset. I am trying to use the indirect function. I have created "Data" in the Name manager. But my formula isn't working: =VLOOKUP($A5,INDIRECT(""&$B5&"!Data"),MATCH(C$2,INDIRECT(""&$B5&"!Data",0),0))
There seems to be an issue in the formula after the Match i.e. the second indirect. It is not connecting the month to the asset. Would appreciate if someone can assist.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You could put examples of what you have on the sheets and the expected result.
 
Upvote 0
Welcome to the Forum!

Let's suppose for the moment you want to look up the sheet 13100, i.e. B5 has the value 13100

Then your formula includes: MATCH(C$2,13100!Data,0), which doesn't make sense if Data is a 2-D array, as it appears.

I am guessing you want to determine the VLOOKUP column based on the first row of Data? In which case perhaps:

=VLOOKUP($A5,INDIRECT($B5&"!Data"),MATCH(C$2,INDEX(INDIRECT($B5&"!Data"),1,),))
 
Upvote 0
Hi, is there a way that I can attach my excel file. I have got help on this from someone. It seems to be working:
=VLOOKUP($A3,INDIRECT("'"&$B3&"'!$A$5:$H$185"),MATCH(C$2,INDIRECT("'"&$B3&"'!$A$4:$H$4"),0),0)
 
Upvote 0
Hi, this formula alsoworks. Actually it is better. I also got help from someone on this:

=VLOOKUP($A5,INDIRECT($B5&"!Data"), MATCH(C$2, INDEX( INDIRECT($B5&"!Data"),1,),0 ), 0)

Theindex formula extracts only first row from Data for MATCH formula
 
Upvote 0
Hi, is there a way that I can attach my excel file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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
Back
Top