Index, match 2 criteria, pull data

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
165
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.

Code:
=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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Office Version
  1. 365
Platform
  1. Windows
Hi,

This should work for you:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">CODE1</td><td style=";">CODE2</td><td style=";">CODE3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">GO</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1060</td><td style="text-align: right;;">543</td><td style="text-align: right;;">1817</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">NB</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1022</td><td style="text-align: right;;">1055</td><td style="text-align: right;;">1638</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">JE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">172</td><td style="text-align: right;;">1907</td><td style="text-align: right;;">782</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">JT</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1363</td><td style="text-align: right;;">1877</td><td style="text-align: right;;">1460</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">L2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1240</td><td style="text-align: right;;">720</td><td style="text-align: right;;">731</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">JA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1390</td><td style="text-align: right;;">1795</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">GG</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1290</td><td style="text-align: right;;">1853</td><td style="text-align: right;;">1116</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">ZV</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">202</td><td style="text-align: right;;">123</td><td style="text-align: right;;">1539</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">GG</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1290</td><td style="text-align: right;;">1853</td><td style="text-align: right;;">1116</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">S5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">247</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">157</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Q2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">477</td><td style="text-align: right;;">127</td><td style="text-align: right;;">1246</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E5</th><td style="text-align:left">=INDEX(<font color="Blue">Data!$C$5:$E$15,MATCH(<font color="Red">Sheet1!$A5,Data!$A$5:$A$15,0</font>),MATCH(<font color="Red">Sheet1!E$4,Data!$C$4:$E$4,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
165
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.

Code:
=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:

ABCDEFG
4CODE1CODE2CODE3
5GO10605431817
6NB102210551638
7JE1721907782
8JT136318771460
9L21240720731
10JA1390179525
11GG129018531116
12ZV2021231539
13GG129018531116
14S52471200157
15Q24771271246

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 14, 2018
Messages
165

ADVERTISEMENT

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

Board Regular
Joined
Apr 14, 2018
Messages
165
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

Board Regular
Joined
Apr 14, 2018
Messages
165
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,666
Messages
5,654,635
Members
418,146
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top