Index & Match on multiple criteria (horizontal & vertical)

[JCTalk]

Hi Guys,

I know how to do a sumproduct for totalling data in this way, but can anyone help with the formula for looking up data from table using X&Y lookup criteria.

Attached a table.

An example lookup would be;

• A4:A4 lookup for "DATA"
• D2:M2 lookup for Feb-15
• D3:M3 lookup for Item5

I'm expecting the value returned from the above example to be the value held by M4.

I didn't really want to use an extra row with months in every cell, but I understand INDEX/MATCH with merged cells to be problematic, so it seemed like an easy solution.

Thanks guys.

 

[tomas]

Hi

I did not understand exactly what you want but I put a formula in cell A4- that might help you

 

[azumi]

Perhaps something like this? using array formula.........

 

[JCTalk]

Hi,

Tomas - Many thanks. I've tried your formula, but it seems to be missing the vertical lookup. The two top X lookups are OK, but I need to also lookup vertically in Column A. I tried adding on another "&" in the same way you have but it doesn't like it. Does vertical have to be done a different way?

Azumi - Many thanks. This one seems to only lookup the month. I need to lookup the month, the item and the vertical column A.

Thanks guys.

 

[Khalid NGO]

Hi,
Is this?

if not, please upload a file with different examples.

Regards,

 

[LOVELY]

Can someone help me on this?

 

[LOVELY]

Can someone help me on this?

 

[azumi]

You should post question on your own thread, juast give you a hand, hope this helps