# Thread: Index / match Thanks:  2 Post #5311008 (1)Post #5311045 (1) Likes:  2 Post #5311045 (1)Post #5311008 (1)

1. ## Index / match

Here is the data which I am trying to extract the dollar amount in D4 from.

Excel 2016 (Windows) 32 bit#E0E0F0 " />#E0E0F0 ;text-align: center;color: #201116">
ABCDEFGHIJKLMNOP
1Tax Credit TypeFederalNLPENSNBONMBSKABBCYTNTNU
215%8.70%9.80%8.79%9.68%5.05%10.80%10.50%10%5.06%6.40%5.90%4%
3-3-5-1-6-4-7
4Basic personal amount (1)(5) \$12,069\$9,414\$9,160\$8,481\$10,264\$10,582\$9,626\$16,065\$19,369\$10,682\$12,069\$14,811\$16,000

#E0E0F0 ;color: #201116">tax_credits_web

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))

2. ## Re: Need help with INDEX / MATCH

ABCDEFGHIJKLMNOP
1Tax Credit TypeFederalNLPENSNBONMBSKABBCYTNTNU
215%8.70%9.80%8.79%9.68%5.05%10.80%10.50%10%5.06%6.40%5.90%4%
3-3-5-1-6-4-7
4Basic personal amount (1)(5)\$12,069 \$9,414 \$9,160 \$8,481 \$10,264 \$10,582 \$9,626 \$16,065 \$19,369 \$10,682 \$12,069 \$14,811 \$16,000
5
612069

Sheet1

Array Formulas
CellFormula
B6{=INDEX(D2:P4,MATCH(TRUE,ISNUMBER(SEARCH("basic",A2:A4)),0),MATCH(TRUE,ISNUMBER(SEARCH("Federal",D1:P1)),0))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

3. ## 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!

4. ## 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))

5. ## Re: Need help with INDEX / MATCH

Thanks again! I'm always learning.

Cheers!