Index, Offset, Match Question in Excel 2007

[Brad Potts]

I am attempting to do some statistical analysis on some inspection reports for my employer.

I have 6 reports exported as excel spreadsheets that I have combined into one workbook, and I want to compile data on the first page of the workbook by doing an index or offset to report the measurement related to the character #.

On the first page of the spread sheet, I have a chart that references several characteristic numbers (#24, #28, #31, etc.). On sheets 2-7, I have the reports that have the characteristic # listed in column B.

The problem I have is that the characteristic numbers on the report are duplicates, and I cannot do a simply VLOOKUP or MATCH/INDEX because the first characteristic # listed is not in the same row as the measurement I need to pull in. See the attached JPEG to see the spatial relationship between the reference and the result.

I know there is a way to use the offset function to accomplish what I'm trying to do, but for the life of me, I can't figure it out.

Please help!

 

[Chihiro]

In B24: =INDEX(INDIRECT("'"&$A27&"'!"&"H:H"),MATCH('16B5232-81'!B$23,INDIRECT("'"&$A27&"'!"&"B:B"),0)+2,0)

Copy and paste to rest.

 

[Brad Potts]

Thank you, but that doesn't seem to do it. I enter that in and I get a request to update the data for 16B5232-81 - I didn't choose a file and it gave me a #REF. I thought it might be trying to pull data from another workbook so I changed that reference to the title of the title of the sheet in the workbook I was working in, and it changed it to a 0.

 

[Chihiro]

My bad, $A27 should be $A24...

See attached.

Odd as "16B5232-81" is the sheet name in your sample workbook.
You can just leave out '16B5232-81'! portion from formula as it isn't required.

 

[Brad Potts]

Success! Thank You!

Any chance you could kinda break down the formula so I can understand?

 

[Chihiro]

INDIRECT("'"&$A24&"'!"&"H:H")
This portion grabs text in $A24 and concatenates with ' & !' And range to make it dynamic reference to sheet and range.
='textstring!'H:H

INDEX, MATCH looks for value in headers (ex B23) and match it in the sheet range. However since it's always off by 2 from the first match found +2 is tacked on.
=INDEX(ResultArray,MATCH(LookupValue,LookupArray)+2))
You don't need column_number as it's only single column array in this case.