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

 

[Haagen]

Hi. This is very helpful when getting data from other worksheets in a summary sheet such as monthly expense data picked up from sheets for each month. However, if the rage reference changes from sheet to sheet the above formula will not automatically change the cell where the data is picked up - as would be the case if new rows were inserted in the table where data is looked up, i.e. if the "...A32:A49" in INDIRECT("'"&G54&"'!A32:A49"),0) changes to A45:A55. How is it possible to make the lookup range dynamic?

 

[chrome700]

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?

I seem to be having the same issue with this formula, and can't get it to work. Any help please?

=INDEX(INDIRECT("'"&EV1&"'!$H$4:$H$2000),MATCH('16.02'!B6,INDIRECT("'"&EV1&"'!$B$4:$B$2000),0))

 

[chrome700]

Think I got it figured out..... needed some more quotation marks around the ranges......

 

[Headline31]

Hello,

I am also having an issue with this thread of formulas and have not been able to solve the issue. However, I am also adding an IF function to the beginning.

Current Worksheet (with formula): 2018 Department Scoreboard
Worksheet with named cell ranges: Score Chart
Named Cell Ranges: FalcOver80 and FScoreOver80

IF cell C8 >.8, THEN search cell C7 in FalcOver80 and RETURN FScoreOver80.

I am having issues referencing the named cell ranges in a different worksheet. This is the formula I currently have:

=IF(C$8>0.8,(INDEX(INDIRECT("'"&Score Chart&'"!"FScoreOver80),MATCH(C$7,INDIRECT("''"&Score Chart&'"!"FalcOver80))))

It gives me an error as it seems that things are not referencing properly to the other worksheets. Please let me know if there is a solution to this or if additional information is needed! Thank you!

 

[Jfabulous29]

Same/Similar Issue INDIRECT MATCH

Hello,

I've been searching for the answer and I'm beating myself up bc I can't seem to figure this out:

I have a workbook with several tabs labeled by week, and my main tab "Forecast Accuracy" has a drop down to select the week (yellow highlight). I need to populate the item sales as they were forecasted in that (yellow) selected week for the specified week (ex: week's 5, 4, 3, 2, and 1). Ex: What was (dropdown selected) week 6's forecast for X item during week 5, week 4, week 3, etc. I have tried a combination of vlookup, index & match, but cannot figure it out.

Any help provided would be very much appreciated!