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

 

[AOB]

Very difficult to identify exactly what it is you're trying to do with any of the above attempts.

Can you describe the problem in simple English?

 

[saadks]

Sorry for complicating my post. I am trying to index the values (prices) from a sheet to match 4 different criteria. Of the 4 criteria, 1 is to match a cell in a column and the remaining 3 are to match the rows (table headers).

I would like the formula to Index = prices from the table $D$4:$AM$199
Match - criteria 1 - country name - the countries are inserted in a column $C$4:$C$199
Match - criteria 2 - type of interview - this is in the row (table header) $D$1:$AM$1
Match - criteria 3 - type of vendor - this is also in a row (below the type of interview) $D$2:$AM$2
Match - criteria 4 - minimum/maximum price - this is again in a row (below the type of vendor) $D$3:$AM$3

I am still not sure if I have tried to simplify my question. Is there a way I can upload my excel sheet?

Thanks a lot for your reply.

Regards,
Sa'ad

I have tried to paste a sample of the data below:

Country	Quantitative/Survey type	Quantitative/Survey type	Quantitative/Survey type
Country	Vendor based in country	Vendor based in country	Vendor based in country
Country	Minimum cost per interview	Average cost per interview	Median cost per interview
United States	$2	$12	$7
Canada	$5	$10	$7

<tbody>
</tbody>

So, I am trying to index the price (say $2) by matching United States, Quantitative/Survey type, Vendor based in country and Minimum cost per interview.

 

[Marcelo Branco]

See if this example helps

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

1​

Type1​

Type1​

Type1​

Type1​

Type1​

Type1​

Type2​

Type2​

Type2​

Type2​

Type2​

Type2​

2​

Vendor1​

Vendor1​

Vendor1​

Vendor2​

Vendor2​

Vendor2​

Vendor1​

Vendor1​

Vendor1​

Vendor2​

Vendor2​

Vendor2​

3​

Minimum​

Average​

Median​

Minimum​

Average​

Median​

Minimum​

Average​

Median​

Minimum​

Average​

Median​

4​

USA​

10​

14​

18​

22​

26​

30​

34​

38​

42​

46​

50​

54​

5​

Canada​

12​

16​

20​

24​

28​

32​

36​

40​

44​

48​

52​

56​

6​

7​

8​

Country​

Type​

Vendor​

Cost​

Result​

9​

USA​

Type1​

Vendor1​

Minimum​

10​

10​

Canada​

Type2​

Vendor2​

Average​

52​

11​

<tbody>
</tbody>

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.

 

[AOB]

Okay - build in small pieces. The INDEX formula works like this :

=INDEX([Range],[Row],[Column])

The

#VALUE!

 

[saadks]

Hi Marcelo,

Thanks a lot for your help! The formula works brilliantly!

Regards,
Sa'ad

 

[saadks]

Okay - build in small pieces. The INDEX formula works like this :

=INDEX([Range],[Row],[Column])

The

#VALUE!

#VALUE!

 

[Marcelo Branco]

Hi Marcelo,

Thanks a lot for your help! The formula works brilliantly!

Regards,
Sa'ad

You are welcome. Thanks for the feedback.

M.

 

[AOB]

Hmmm, I get a similar result... My bad, I was matching across the entire row instead of keeping the ranges consistent.

This amended formula should work :

=INDEX('Prices Consolidated'!$C$1:$AM$199,MATCH('Query Sheet'!$B$3,'Prices Consolidated'!$C$1:$C$199,0),MATCH(1,('Prices Consolidated'!$C$1:$AM1='Query Sheet'!$D$3)*('Prices Consolidated'!$C$2:$AM$2='Query Sheet'!$B$6)*('Prices Consolidated'!$C$3:$AM$3='Query Sheet'!$D$8),0))

 

[grueda83]

Hello, i've looked into different array formulas and I have not been able to make one work where it gives me the overlaps for sets of data that are across rows rather than columns when the data is arraigned as follows. Wondering if someone out there has an answer where we can show the overlaps (index match) across each rows:

	A	B	C	D	E	Look Up
Item 1	10.14	14.14	14.14	10.31	8	8	E
Item 2	1382	3470	3470	1447	729	3470	B	C
Item 3	64	200	200	64	64	64	B	D	E

<colgroup><col><col span="5"><col><col><col><col></colgroup><tbody>
</tbody>