INDEX - INDIRECT - MATCH Functions

[GLS_429]

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

 

[Domenic]

Try...

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

 

[GLS_429]

Domenic,

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

Until next time,

GLS

 

[Domenic]

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

 

[jackdemo]

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

=INDEX(CONCATENATE("forecast_control_sheets.xlsm!","", INDIRECT(ADDRESS(8,3)),""),MATCH($D8,[forecast_control_sheets.xlsm]CLPSubContractorRates!$F$4:$F$70,0),MATCH(I$1,OFFSET(INDIRECT(TEXT($H8,"")),1,0,1,5)))

Thoughts?

 

[langelese19]

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 4^th 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

 

[AlanY]

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

=INDEX(CONCATENATE("forecast_control_sheets.xlsm!","", INDIRECT(ADDRESS(8,3)),""),MATCH($D8,[forecast_control_sheets.xlsm]CLPSubContractorRates!$F$4:$F$70,0),MATCH(I$1,OFFSET(INDIRECT(TEXT($H8,"")),1,0,1,5)))

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 4^th 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.

 

[langelese19]

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.

 

[Domenic]

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?

 

[langelese19]

YES!!!

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