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

 

[alext900]

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:

[TABLE="width: 512"]
<tbody>[TR]
[TD][/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD]Look Up[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]10.14[/TD]
[TD]14.14[/TD]
[TD]14.14[/TD]
[TD]10.31[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD] E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]1382[/TD]
[TD]3470[/TD]
[TD]3470[/TD]
[TD]1447[/TD]
[TD]729[/TD]
[TD]3470[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]64[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]64[/TD]
[TD]64[/TD]
[TD]64[/TD]
[TD] B[/TD]
[TD] D[/TD]
[TD] E[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

Has anyone found a solution for this yet? This is puzzling me too!

Thanks,

 

[Aladin Akyurek]

Hi,

Has anyone found a solution for this yet? This is puzzling me too!

Thanks,

Are those A, B, C, etc. column letters or symbols that you enter yourself in row 1?

 

[alext900]

Are those A, B, C, etc. column letters or symbols that you enter yourself in row 1?

They are manually entered column headings.

Regards,

 

[Aladin Akyurek]

They are manually entered column headings.

Regards,

In H2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$1:$F$1;SMALL(IF($B2:$F2=$G2;COLUMN($B$1:$F$1)-COLUMN($B$1)+1);COLUMNS($H2:H2)));"")

Replace the semi-colons with comma's if you are on an American system.

 

[alext900]

In H2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$1:$F$1;SMALL(IF($B2:$F2=$G2;COLUMN($B$1:$F$1)-COLUMN($B$1)+1);COLUMNS($H2:H2)));"")

Replace the semi-colons with comma's if you are on an American system.

Fantastic thank you! I'm now trying to replicate those formulae into my worksheet. I have it working all fine when my row headings are numbers but as soon as I change them to text (which is what I need) the formulae stop working. Please can you advise?

[TABLE="width: 606"]
<colgroup><col><col span="7"></colgroup><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]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value to search:[/TD]
[TD][/TD]
[TD][/TD]
[TD]Row[/TD]
[TD]Column[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Garden[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pizza[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dog[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Car[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fish[/TD]
[TD]#VALUE!
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula in ROW is: {=IFERROR(IF(ROWS($D$12:$D12)>COUNTIF($B$2:$H8,$A$12),"",INDEX($A$2:$A$8,SMALL(IF($B$2:$H$8=$A$12,ROW($B$2:$H$8)-ROW($B$2)+1),ROWS($D$12:$D12)))),"")}

and in COLUMN is: {=IF(D12="","",INDEX($B$1:$H$1,INDEX(SMALL(IF(OFFSET($B$2:$H$2,D12-1,0)=$A$12,(COLUMN($B$2:$H$8)-COLUMN($B$2)+1)),COUNTIF($D$12:$D12,D12)),1,1)))}

Thank you

 

[Aladin Akyurek]

Would you care to post your own sample along with the expected output without any reference to a formula?

 

[alext900]

Sorry, not quite sure what you mean?

I attached a sample of my worksheet above. The 'Row' column is working correctly and pulling through all the rows with 'A' in, but the 'Column' column isn't working. I have included my expected outcome values:

[TABLE="width: 810"]
<colgroup><col><col span="8"><col><col></colgroup><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][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Garden[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pizza[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Value to search:[/TD]
[TD]Row[/TD]
[TD]Column[/TD]
[TD]Expected Outcome[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]D[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]F[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]G[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Garden[/TD]
[TD]#VALUE![/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pizza[/TD]
[TD]#VALUE![/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pizza[/TD]
[TD]#VALUE![/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dog[/TD]
[TD]#VALUE![/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Car[/TD]
[TD]#VALUE![/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fish[/TD]
[TD]#VALUE![/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fish[/TD]
[TD]#VALUE![/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]

*Please note the table has slightly changed from the previous one posted in this thread to highlight the formula needs to be able to pull duplicates.

Regards,

 

[Aladin Akyurek]

This seems to be the input...

[Table="width:, class:grid"][tr][td]Row\Col[/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][/tr][tr][td]

1​

[/td][td] [/td][td] A[/td][td] X[/td][td] A[/td][td] A[/td][td] X[/td][td] A[/td][td] A[/td][/tr]
[tr][td]

2​

[/td][td] Garden[/td][td] X[/td][td] X[/td][td] A[/td][td] X[/td][td] X[/td][td] X[/td][td] X[/td][/tr]
[tr][td]

3​

[/td][td] House[/td][td] X[/td][td] X[/td][td] X[/td][td] X[/td][td] X[/td][td] X[/td][td] X[/td][/tr]
[tr][td]

4​

[/td][td] Pizza[/td][td] A[/td][td] X[/td][td] X[/td][td] X[/td][td] X[/td][td] X[/td][td] A[/td][/tr]
[tr][td]

5​

[/td][td] Dog[/td][td] X[/td][td] X[/td][td] X[/td][td] X[/td][td] A[/td][td] X[/td][td] X[/td][/tr]
[tr][td]

6​

[/td][td] Car[/td][td] X[/td][td] X[/td][td] X[/td][td] A[/td][td] X[/td][td] X[/td][td] X[/td][/tr]
[tr][td]

7​

[/td][td] Fish[/td][td] X[/td][td] A[/td][td] X[/td][td] X[/td][td] X[/td][td] A[/td][td] X[/td][/tr]
[/table]

I'm absolutely not clear about the out put you want. If you are going to post the desired output, do not refer to any formula outcome, just say what it must be.

 

[63falcondude]

To put post #17 into perspective:

Excel 2010
	A	B	C	D	E	F	G	H
1		A	B	C	D	E	F	G
2	1	A	X	A	A	X	A	A
3	Garden	X	X	A	X	X	X	X
4	House	X	X	X	X	X	X	X
5	Pizza	A	X	X	X	X	X	A
6	Dog	X	X	X	X	A	X	X
7	Car	X	X	X	A	X	X	X
8	Fish	X	A	X	X	X	A	X
9
10
11	Value to Search			Row	Column	Expected Outcome
12	A			1	A	A
13				1	C	C
14				1	D	D
15				1	F	F
16				1	G	G
17				Garden	#VALUE!	C
18				Pizza	#VALUE!	A
19				Pizza	#VALUE!	G
20				Dog	#VALUE!	E
21				Car	#VALUE!	D
22				Fish	#VALUE!	B
23				Fish	#VALUE!	F
Sheet2

Cell Formulas
Range	Formula
D12	{=IFERROR(IF(ROWS($D$12:$D12)>COUNTIF($B$2:$H8,$A$12),"",INDEX($A$2:$A$8,SMALL(IF($B$2:$H$8=$A$12,ROW($B$2:$H$8)-ROW($B$2)+1),ROWS($D$12:$D12)))),"")}
E12	{=IF(D12="","",INDEX($B$1:$H$1,INDEX(SMALL(IF(OFFSET($B$2:$H$2,D12-1,0)=$A$12,(COLUMN($B$2:$H$8)-COLUMN($B$2)+1)),COUNTIF($D$12:$D12,D12)),1,1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AAKZ76]

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