Index, match 2 criteria, pull data

[Glasgowsmile]

Hello!

Trying to get an index match, or whatever formula I need to get a sheet to work.

Code currently pulls N/A.

I'm trying to match the Heading Title then subtitle and finally pull the data around it.

=INDEX(Data!C5:M40,MATCH(1,(Sheet1!E4=Data!C4:N4)*(Sheet1!A5=Data!A5:A31),0))

Here is the spreadsheet in case you need to reference it.

https://www.dropbox.com/s/ymsc2h5gty7xgmc/CodeExample.xlsx?dl=0

 

[Aryatect]

Hi,

This should work for you:

Book1
	A	B	C	D	E	F	G
4					CODE1	CODE2	CODE3
5	GO				1060	543	1817
6	NB				1022	1055	1638
7	JE				172	1907	782
8	JT				1363	1877	1460
9	L2				1240	720	731
10	JA				1390	1795	25
11	GG				1290	1853	1116
12	ZV				202	123	1539
13	GG				1290	1853	1116
14	S5				247	1200	157
15	Q2				477	127	1246
Sheet1

Cell Formulas
Range	Formula
E5	=INDEX(Data!$C$5:$E$15,MATCH(Sheet1!$A5,Data!$A$5:$A$15,0),MATCH(Sheet1!E$4,Data!$C$4:$E$4,0))

 

[Glasgowsmile]

Thanks - that worked great, but I tried this same formula on another document and I'm getting an #N/A error. What kinds of things would cause that so I can try to trouble shoot it?

Here is the code I used for the new sheet - virtually an identical setup to this example sheet.

=INDEX(DlyRateCodes1!$D$27:$K$46,MATCH('Rate Checker'!H$3,DlyRateCodes1!$D$25:$K$25,0),MATCH('Rate Checker'!$A5,DlyRateCodes1!$B$27:$B$48,0))

Hi,

This should work for you:

	A	B	C	D	E	F	G
CODE1	CODE2	CODE3
GO
NB
JE
JT
L2
JA
GG
ZV
GG
S5
Q2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1060[/TD]
[TD="align: right"]543[/TD]
[TD="align: right"]1817[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1022[/TD]
[TD="align: right"]1055[/TD]
[TD="align: right"]1638[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]1907[/TD]
[TD="align: right"]782[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1363[/TD]
[TD="align: right"]1877[/TD]
[TD="align: right"]1460[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1240[/TD]
[TD="align: right"]720[/TD]
[TD="align: right"]731[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1390[/TD]
[TD="align: right"]1795[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1290[/TD]
[TD="align: right"]1853[/TD]
[TD="align: right"]1116[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]1539[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1290[/TD]
[TD="align: right"]1853[/TD]
[TD="align: right"]1116[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]247[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]157[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]477[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]1246[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=INDEX(Data!$C$5:$E$15,MATCH(Sheet1!$A5,Data!$A$5:$A$15,0),MATCH(Sheet1!E$4,Data!$C$4:$E$4,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

I think you have applied in wrong order, you have done INDEX(Range,MATCH for Column,MATCH for Row), it should be INDEX(Range, MATCH for Row, MATCH for Column)

 

[Glasgowsmile]

Something I do want to mention.

The Cells that are being used to Match -- H$3 and $A5, those are not text cells, they have formulas in them. Would that cause an error?

 

[Glasgowsmile]

I think you have applied in wrong order, you have done INDEX(Range,MATCH for Column,MATCH for Row), it should be INDEX(Range, MATCH for Row, MATCH for Column)

Thanks, I've tried both ways and get N/A each time -- wondering if since the says are a forumla, pulling data from another sheet to that cell, if that's the issue with this formula? Is it not able to correctly see the reference data?

 

[Glasgowsmile]

Thanks, I've tried both ways and get N/A each time -- wondering if since the says are a forumla, pulling data from another sheet to that cell, if that's the issue with this formula? Is it not able to correctly see the reference data?

so... I typed the Data in the cell instead of pulling it from another sheet and the INDEX/Match worked perfectly but that creates a new problem sadly. How do I get it to look at the actual reference data in the cell and not the formula?