Complex formula to select data field within data-table

[konfis]

Hey folks,

I am desperate to find a way to select a data field out of a data table. The table has two header rows and one header column. At this point, I think it is best to have a look in the attached file. In this file you'll find an example problem, that eventually need to be expanded across a larger data set.

The data table is an input from a non-Excel source and can not be altered prior to import to Excel. Thus the joint cells in header row 1 are a given. A selection with respect to the two header rows and the header column is made to get the data from the repective field. There are some limitations to the data-set and the way of accomplishing the goal:
- the ranges of header rows and columns are variable
- if possible, no macro should be used. UDF would be fine

Is there any way to build a formula that can accomplish this?

Thank you very much for your help.

Kind regards,
Konfis

[JohnTopley]

Try

=INDEX($B$5:$J$7,MATCH($M7,$A$5:$A$7,0),MATCH($M5,$B$3:$J$3,0)+MATCH($M6,$B$4:$J$4,0)-1)


UDF is a macro.

[konfis]

Hey John,

thanks for the quick solution. It works great in the sample data. I will test it in the larger data set and will let you know if it works.

Of course, you are right, UFD is macro!

Kind regards!