Index / match

leopardhawk

Active Member
Joined
May 31, 2007
Messages
428
Office Version
2016
Platform
Windows
Here is the data which I am trying to extract the dollar amount in D4 from.

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0 " /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0 ;text-align: center;color: #201116"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">1</td><td style=";">Tax Credit Type</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Federal</td><td style=";">NL</td><td style=";">PE</td><td style=";">NS</td><td style=";">NB</td><td style=";">ON</td><td style=";">MB</td><td style=";">SK</td><td style=";">AB</td><td style=";">BC</td><td style=";">YT</td><td style=";">NT</td><td style=";">NU</td></tr><tr ><td style="color: #201116;text-align: center;">2</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;"></td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;"></td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;"></td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">15%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">8.70%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">9.80%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">8.79%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">9.68%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">5.05%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">10.80%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">10.50%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">10%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">5.06%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">6.40%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">5.90%</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">4%</td></tr><tr ><td style="color: #201116;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-3</td><td style="text-align: right;;"></td><td style="text-align: right;;">-5</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-7</td></tr><tr ><td style="color: #201116;text-align: center;">4</td><td style="color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">Basic personal amount (1)(5) </td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;"></td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;"></td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$12,069</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$9,414</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$9,160</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$8,481</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$10,264</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$10,582</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$9,626</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$16,065</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$19,369</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$10,682</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$12,069</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$14,811</td><td style="text-align: right;color: rgb(0, 97, 0);background-color: rgb(198, 239, 206);;">$16,000</td></tr></tbody></table><p style="width:9em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0 ;color: #201116">tax_credits_web</p><br /><br />Here is my formula which is returning the #N/A error. Does anyone know what I can do to resolve this? Thanks!

Code:
=INDEX(D2:P4,MATCH(TRUE,ISNUMBER(SEARCH(A2:A4,basic)),0),MATCH(TRUE,ISNUMBER(SEARCH(D1:P1,Federal)),0))
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
Re: Need help with INDEX / MATCH

<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 /><col /><col /><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><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Tax Credit Type</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Federal</td><td style=";">NL</td><td style=";">PE</td><td style=";">NS</td><td style=";">NB</td><td style=";">ON</td><td style=";">MB</td><td style=";">SK</td><td style=";">AB</td><td style=";">BC</td><td style=";">YT</td><td style=";">NT</td><td style=";">NU</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">15%</td><td style="text-align: right;;">8.70%</td><td style="text-align: right;;">9.80%</td><td style="text-align: right;;">8.79%</td><td style="text-align: right;;">9.68%</td><td style="text-align: right;;">5.05%</td><td style="text-align: right;;">10.80%</td><td style="text-align: right;;">10.50%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">5.06%</td><td style="text-align: right;;">6.40%</td><td style="text-align: right;;">5.90%</td><td style="text-align: right;;">4%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-3</td><td style="text-align: right;;"></td><td style="text-align: right;;">-5</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Basic personal amount (1)(5)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$12,069 </td><td style="text-align: right;;">$9,414 </td><td style="text-align: right;;">$9,160 </td><td style="text-align: right;;">$8,481 </td><td style="text-align: right;;">$10,264 </td><td style="text-align: right;;">$10,582 </td><td style="text-align: right;;">$9,626 </td><td style="text-align: right;;">$16,065 </td><td style="text-align: right;;">$19,369 </td><td style="text-align: right;;">$10,682 </td><td style="text-align: right;;">$12,069 </td><td style="text-align: right;;">$14,811 </td><td style="text-align: right;;">$16,000 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</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="text-align: right;;"></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="text-align: right;;"></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="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">12069</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="text-align: right;;"></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="text-align: right;;"></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></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>Array 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)">B6</th><td style="text-align:left">{=INDEX(<font color="Blue">D2:P4,MATCH(<font color="Red">TRUE,ISNUMBER(<font color="Green">SEARCH(<font color="Purple">"basic",A2:A4</font>)</font>),0</font>),MATCH(<font color="Red">TRUE,ISNUMBER(<font color="Green">SEARCH(<font color="Purple">"Federal",D1:P1</font>)</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
428
Office Version
2016
Platform
Windows
Re: Need help with INDEX / MATCH

Scott T, thank you so much! As a follow-up, and this is a hypothetical question, what would happen if there were two occurrences of the word 'Federal' in row A, ie. let's say that 'Federal' was also found in cell H1? Is there a way to prevent the formula from returning the #N/A error? Thanks!
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
Re: Need help with INDEX / MATCH

Match would find the first occurrence of Federal and still return 12,069.

You should only get the N/A if one of the words you search for is not found or you do not use CTRL+SHIFT+ENTER.

If you do not use search then you do not need CSE

You could use wildcards
Code:
=INDEX(D2:P4,MATCH("*Basic*",A2:A4,0),MATCH("federal",D1:P1,0))
or enter the whole text
Code:
=INDEX(D2:P4,MATCH("Basic personal amount (1)(5)",A2:A4,0),MATCH("federal",D1:P1,0))
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
428
Office Version
2016
Platform
Windows
Re: Need help with INDEX / MATCH

Thanks again! I'm always learning.

Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,574
Messages
5,469,475
Members
406,655
Latest member
pwilson06

This Week's Hot Topics

Top