Index / match

[leopardhawk]

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

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Tax Credit Type			Federal	NL	PE	NS	NB	ON	MB	SK	AB	BC	YT	NT	NU
2				15%	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
4	Basic 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!

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

 

[Scott T]

Re: Need help with INDEX / MATCH

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Tax Credit Type			Federal	NL	PE	NS	NB	ON	MB	SK	AB	BC	YT	NT	NU
2				15%	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
4	Basic 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
6		12069
Sheet1

Cell Formulas
Range	Formula
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.

 

[leopardhawk]

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]

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

=INDEX(D2:P4,MATCH("*Basic*",A2:A4,0),MATCH("federal",D1:P1,0))

or enter the whole text

=INDEX(D2:P4,MATCH("Basic personal amount (1)(5)",A2:A4,0),MATCH("federal",D1:P1,0))

 

[leopardhawk]

Re: Need help with INDEX / MATCH

Thanks again! I'm always learning.

Cheers!