Index Match Multiple Criteria

[SBlasberg]

Hello,

I'm in need of some help with a formula. I've attached a dummy sheet to help explain what I'm trying to get to.

I have multiple criteria that I need to use, one referencing a column, and two referencing rows from my data table. The goal is to pull the "50" back from the data table using following criteria:

1) Find Summary tab A3 in Data tab column A
2) Find Summary tab B1 in Data tab row 1
3) Find Summary tab B2 in Data tab row 2

Please let me know if I can clarify more. Much appreciated!!

 

[Peter A.]

Hello,

I'm in need of some help with a formula. I've attached a dummy sheet to help explain what I'm trying to get to.

I have multiple criteria that I need to use, one referencing a column, and two referencing rows from my data table. The goal is to pull the "50" back from the data table using following criteria:

1) Find Summary tab A3 in Data tab column A
2) Find Summary tab B1 in Data tab row 1
3) Find Summary tab B2 in Data tab row 2

Please let me know if I can clarify more. Much appreciated!!

Try it:

{=SUMPRODUCT((Data!$A$3:$A$14=Summary!A3)*(Data!$B$1:$Y$1=Summary!B1)*(Data!$B$2:$Y$2=Summary!B2)*(Data!B3:Y14))}

array formula

 

[SBlasberg]

Hello,

Might there be a less taxing way to do this on excel? Mine "runs out of resources" when I try this in my real workbook. I have almost 100 columns and a few thousand rows of data.

Also, I drop in new data weekly, and is not always a set amount of rows, so the last piece (Data!B3:Y14) may not hold true week over week.

Thanks so much!

 

[Chihiro]

Unfortunately, with cross tab table structure, most formula will be resource intensive.

If at all possible, flat table structure is desirable. It may not help formula efficiency depending on requirement, but you can use other data analysis tools to accomplish your need. Such as, PivotTable, PowerQuery, MS Query, Advanced Filter etc.

See link for detail on why flat table is preferable to cross tab (not tested, but it also has VBA tool to convert cross tab to flat table).
https://davidwpowell.wordpress.com/...vert-an-excel-crosstab-to-a-flat-file-a-list/

 

[bosco_yip]

This is a 2 ways Lookup question, try to use Lookup function which is faster than the Math function.

=INDEX(Data!B3:Y14,MATCH(Summary!A3,Data!$A$3:$A$14,0),MATCH(1,INDEX((Data!$B$1:$Y$1=Summary!B1)*(Data!$B$2:$Y$2=Summary!B2),0),0))

Regards