Index / match

leopardhawk

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


Excel 2016 (Windows) 32 bit
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
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))
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Re: Need help with INDEX / MATCH


Book1
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
Cell Formulas
RangeFormula
B6{=INDEX(D2:P4,MATCH(TRUE,ISNUMBER(SEARCH("basic",A2:A4)),0),MATCH(TRUE,ISNUMBER(SEARCH("Federal",D1:P1)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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!
 
Upvote 0
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))
 
Upvote 0
Re: Need help with INDEX / MATCH

Thanks again! I'm always learning.

Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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
Back
Top