# INDEX - INDIRECT - MATCH Functions

#### GLS_429

##### New Member
Finally breaking down and asking for help with the following function/brick wall.

I'm currently using the match function to look up a dollar amount per insulation diameter and thickness. Each different tab is a different type of insulation.

I've always had to hardcode in the "Tab Worksheet Name" to look up a value. I'm working with multiple sheets now and want to be able to take advantage of the drag feature to copy cells. The first function is what I am currently using (and works), but can't seem to get the second to work?

In this particular example, B8 would be the cell that contains the tab name (Type of insulation). C8 and D8 are the Diameter and Thickness used to match up and obtain the price.

=INDEX('Min Wool Pipe Cvr'!\$A\$4:\$J\$44,MATCH(C8,'Min Wool Pipe Cvr'!\$A\$4:A\$44,0),MATCH(D8,'Min Wool Pipe Cvr'!\$A\$4:J\$4,0))

=INDEX(INDIRECT("'" & B8 & "'!" & \$A\$4:\$J\$44),MATCH(C8,(INDIRECT("'"& B8 & "'!" & \$A\$4:\$A\$44)),0),MATCH(D8,(INDIRECT("'" & B8 & "'!" & \$A\$4:\$J\$4)),0))

Thank you in advance to anyone that can help,

GLS

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try...

=INDEX(INDIRECT("'"&B8 &"'!A4:J44"),MATCH(C8,INDIRECT("'"&B8&"'!A4:A44"),0),MATCH(D8,INDIRECT("'"&B8&"'!A4:J4"),0))

Domenic,

Thanks so much, it worked perfect!!! I should have asked earlier.

Until next time,

GLS

You're very welcome! And thanks for the feedback!

Hello, I'm having a similar problem where the range reference refers to a single cell. The "Matches" seem to work fine, but referencing C8 give me trouble. Have attempted multiple variations...

=INDEX(INDIRECT("'"&G23 &"'!C8"),MATCH(\$D8,[forecast_control_sheets.xlsm]CLPSubContractorRates!\$F\$4:\$F\$70,0),MATCH(J\$1,OFFSET(INDIRECT(TEXT(\$H8,"")),1,0,1,5),0))

Thoughts?

Hello, same issue as above - I have spent nearly a month trawling every single forum in existence for a solution, but I think it's time to admit that I really do need help (please!).

I am trying to combine INDEX/INDIRECT/MATCH, I have 4 worksheets, 3 of them contain a table of data (A32:B49 my lookup array) for various suppliers, the 4th is my main sheet called “Material Schedule” which contains my match lookup values in column F and a drop down list where I select the name of the sheet that I want Excel to search through -- in column G.

My INDEX + MATCH formula works perfectly (when I manually enter the name of the sheet) the issue arises whenever I try to introduce INDIRECT into the mix.
As I’ve altered the formula so many times now I have seen all the possible error messages thrown up at some point, the INDIRECT + MATCH function that works correctly is:

=INDEX('Stanton Bonna Data'!\$B\$32:\$B\$49,MATCH('Material Schedule'!F54,'Stanton Bonna Data'!\$A\$32:\$A\$49,0))

(Stanton Bonna is one of the 3 suppliers/worksheet names)

The soul-destroying-life-sucking-diabetes-inducing-incorrect-one is:

=INDEX(INDIRECT("'"&G54 &"'!\$B\$32:\$B\$49"),MATCH(F54,INDIRECT("'"&G54&"'!\$A\$32:\$A\$49"),0))

Can someone advise what I am doing wrong with INDIRECT so I can retain some modicum of sanity please?
Please let me know if any further information is required - using 2013 on Win 7.

Thanks

Hello, I'm having a similar problem where the range reference refers to a single cell. The "Matches" seem to work fine, but referencing C8 give me trouble. Have attempted multiple variations...

=INDEX(INDIRECT("'"&G23 &"'!C8"),MATCH(\$D8,[forecast_control_sheets.xlsm]CLPSubContractorRates!\$F\$4:\$F\$70,0),MATCH(J\$1,OFFSET(INDIRECT(TEXT(\$H8,"")),1,0,1,5),0))

Thoughts?

Hello, same issue as above - I have spent nearly a month trawling every single forum in existence for a solution, but I think it's time to admit that I really do need help (please!).

I am trying to combine INDEX/INDIRECT/MATCH, I have 4 worksheets, 3 of them contain a table of data (A32:B49 my lookup array) for various suppliers, the 4th is my main sheet called “Material Schedule” which contains my match lookup values in column F and a drop down list where I select the name of the sheet that I want Excel to search through -- in column G.

My INDEX + MATCH formula works perfectly (when I manually enter the name of the sheet) the issue arises whenever I try to introduce INDIRECT into the mix.
As I’ve altered the formula so many times now I have seen all the possible error messages thrown up at some point, the INDIRECT + MATCH function that works correctly is:

=INDEX('Stanton Bonna Data'!\$B\$32:\$B\$49,MATCH('Material Schedule'!F54,'Stanton Bonna Data'!\$A\$32:\$A\$49,0))

(Stanton Bonna is one of the 3 suppliers/worksheet names)

The soul-destroying-life-sucking-diabetes-inducing-incorrect-one is:

=INDEX(INDIRECT("'"&G54 &"'!\$B\$32:\$B\$49"),MATCH(F54,INDIRECT("'"&G54&"'!\$A\$32:\$A\$49"),0))

Can someone advise what I am doing wrong with INDIRECT so I can retain some modicum of sanity please?
Please let me know if any further information is required - using 2013 on Win 7.

Thanks

this might be yours problem;

INDIRECT unfortunately only works with within the workbook or with workbooks that are open during the calculation time. If the external workbook is closed, it will result in a <code>#REF!</code> error.

Background: For normal external links, Excel stores not only the address to the external cell, but also the last known value in the saved version of the file. This way, when opening the file, Excel can calculate the the whole workbook, even if the external source is not available.

INDIRECT however is a volatile function, i.e. Excel has to calculate it every time a calculation is run (while a "normal" function only has to be recalculated if any of the predecessor cells change). Therefore, Excel cannot store the value of the reference and thus results the <code>#REF!</code> error.

Apologies for the confusion, but mine are all within the same workbook.
I feel like I'm the only person in the world who can't get INDIRECT to work, it's a lonely place to be.

If anyone figures this out I promise to write them a poem and pay them with tears of gratitude.

Apologies for the confusion, but mine are all within the same workbook.
I feel like I'm the only person in the world who can't get INDIRECT to work, it's a lonely place to be.

If anyone figures this out I promise to write them a poem and pay them with tears of gratitude.

Your formula seems fine, except that the reference to F54 doesn't include the reference to your sheet called "Material Schedule", as per your original formula. So maybe your formula should be...

=INDEX(INDIRECT("'"&G54&"'!B32:B49"),MATCH('Material Schedule'!F54,INDIRECT("'"&G54&"'!A32:A49"),0))

Does this help?

YES!!!

Thank you SO much, I almost cried with joy.
No one at work understands my elation right now - YOU'RE THE BEST!

Replies
10
Views
211
Replies
2
Views
73
Replies
5
Views
188
Replies
3
Views
149
Replies
4
Views
342

1,206,757
Messages
6,074,760
Members
446,084
Latest member
WalmitAal

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

### Which adblocker are you using?

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

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