Index match with multiple criteria across rows and columns

[saadks]

Hi,

I have been trying to use index match for 4 criteria across 1 row and 3 columns. I have tried multiple approaches, but get different errors.

1. =INDEX('Prices Consolidated'!$D$4:$AM$199,MATCH('Query Sheet'!$B$3&'Query Sheet'!$D$3&'Query Sheet'!$B$6&'Query Sheet'!$B$8,'Prices Consolidated'!$C$4:$C$199&'Prices Consolidated'!$D$1:$AM$1&'Prices Consolidated'!$D$2:$AM$2&'Prices Consolidated'!$D$3:$AM$3,0)) (array formula - returning value #N/A)

2. =INDEX('Prices Consolidated'!$D$4:$AM$199,MATCH(1,('Query Sheet'!$B$3='Prices Consolidated'!$C$4:$C$199)*('Query Sheet'!$D$3='Prices Consolidated'!$D$1:$AM$1)*('Query Sheet'!$B$6='Prices Consolidated'!$D$2:$AM$2)*($B$8='Prices Consolidated'!$D$3:$AM$3),0)) (array formula - returning value #N/A)

3. =index('Prices Consolidated'!$D$4:$AM$199,match('Query Sheet'!$B$3,'Prices Consolidated'!$C$4:$C$199,0),match('Query Sheet'!$D$3,'Prices Consolidated'!$D$1:$AM$1,0),match('Query Sheet'!$B$6,'Prices Consolidated'!$D$2:$AM$2,0),match('Query Sheet'!D8,'Prices Consolidated'!$D$3:$AM$3,0)) (array formula - it gives me an error - you have entered too many arguments for this function)

4. Also tried =INDEX('Prices Consolidated'!$D$4:$AM$199,MATCH('Query Sheet'!$B$3,'Prices Consolidated'!$C$4:$C$199,0)&MATCH('Query Sheet'!$D$3,'Prices Consolidated'!$D$1:$AM$1,0)&MATCH('Query Sheet'!$B$6,'Prices Consolidated'!$D$2:$AM$2,0)&MATCH('Query Sheet'!D8,'Prices Consolidated'!$D$3:$AM$3,0)) (array formula - but it gives me an error - #REF!)

I have cross checked if the cells match - in terms of spacing, spelling errors, etc. No issues there. I hope my query is not too confusing - this is my first post here on the forum. Can't quite figure out what is going wrong with the formula.

Thank you so much in advance for you help.

Sa'ad

 

[Marcelo Branco]

Sorry not work for me. I've copied the example given by Marcelo Branco, unfortunately the formula is not working. Any reason?

Welcome to Mr Excel

Have you confirmed the formula with Ctrl+Shift+Enter simultaneously (not just Enter)?

If you are not familiar with array formulas take a look at
Array Formulas

M.

 

[AAKZ76]

Yes, It is work for me. I've forgotten to press the Ctrl+Shift+Enter simultaneously. TQ Marcelo Branco.
What will be the formula if the match criteria is in the opposite ways meaning there will be two columns and single row to match in the data table.

Please advise.

 

[Marcelo Branco]

Yes, It is work for me. I've forgotten to press the Ctrl+Shift+Enter simultaneously. TQ Marcelo Branco.
What will be the formula if the match criteria is in the opposite ways meaning there will be two columns and single row to match in the data table.

Please advise.

I think you should create a new thread - try to show a data sample along with expected result(s).

M.

 

[AAKZ76]

I manage to solve the problem. I just swap the formula between the row and column criteria.

Thanks.

 

[singh1214]

Hi friend,

Please also try below formula

=SUMPRODUCT(($A$4:$A$5=A9)*($B$1:$M$1=B9)*($B$2:$M$2=C9)*($B$3:$M$3=D9)*($B$4:$M$5))

Hope this would solve your problem.

Regards and Enjoy

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Type1​

[/TD]
[TD]

Type1​

[/TD]
[TD]

Type1​

[/TD]
[TD]

Type1​

[/TD]
[TD]

Type1​

[/TD]
[TD]

Type1​

[/TD]
[TD]

Type2​

[/TD]
[TD]

Type2​

[/TD]
[TD]

Type2​

[/TD]
[TD]

Type2​

[/TD]
[TD]

Type2​

[/TD]
[TD]

Type2​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

Vendor1​

[/TD]
[TD]

Vendor1​

[/TD]
[TD]

Vendor1​

[/TD]
[TD]

Vendor2​

[/TD]
[TD]

Vendor2​

[/TD]
[TD]

Vendor2​

[/TD]
[TD]

Vendor1​

[/TD]
[TD]

Vendor1​

[/TD]
[TD]

Vendor1​

[/TD]
[TD]

Vendor2​

[/TD]
[TD]

Vendor2​

[/TD]
[TD]

Vendor2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

Minimum​

[/TD]
[TD]

Average​

[/TD]
[TD]

Median​

[/TD]
[TD]

Minimum​

[/TD]
[TD]

Average​

[/TD]
[TD]

Median​

[/TD]
[TD]

Minimum​

[/TD]
[TD]

Average​

[/TD]
[TD]

Median​

[/TD]
[TD]

Minimum​

[/TD]
[TD]

Average​

[/TD]
[TD]

Median​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

USA​

[/TD]
[TD]

10​

[/TD]
[TD]

14​

[/TD]
[TD]

18​

[/TD]
[TD]

22​

[/TD]
[TD]

26​

[/TD]
[TD]

30​

[/TD]
[TD]

34​

[/TD]
[TD]

38​

[/TD]
[TD]

42​

[/TD]
[TD]

46​

[/TD]
[TD]

50​

[/TD]
[TD]

54​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Canada​

[/TD]
[TD]

12​

[/TD]
[TD]

16​

[/TD]
[TD]

20​

[/TD]
[TD]

24​

[/TD]
[TD]

28​

[/TD]
[TD]

32​

[/TD]
[TD]

36​

[/TD]
[TD]

40​

[/TD]
[TD]

44​

[/TD]
[TD]

48​

[/TD]
[TD]

52​

[/TD]
[TD]

56​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

Country​

[/TD]
[TD]

Type​

[/TD]
[TD]

Vendor​

[/TD]
[TD]

Cost​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

USA​

[/TD]
[TD]

Type1​

[/TD]
[TD]

Vendor1​

[/TD]
[TD]

Minimum​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

Canada​

[/TD]
[TD]

Type2​

[/TD]
[TD]

Vendor2​

[/TD]
[TD]

Average​

[/TD]
[TD]

52​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Put the criteria in A9:D9 and in A10:D10 (identical to the headers in rows 1, 2 and 3)

Array formula in E9 copied down
=INDEX($B$4:$M$5,MATCH(A9,$A$4:$A$5,0),MATCH(1,IF($B$1:$M$1=B9,IF($B$2:$M$2=C9,IF($B$3:$M$3=D9,1))),0))

confirmed with Ctrl+Shift+Enter

Hope this helps

M.[/QUOTE]