Index and Match is returning #VALUE

[Sean15]

Excel 2010
	A	B	C
18	First Name	Last Name	Amt
19	John	Johnson	15,000.00
20	Smith	Smithson	850.00
21	James	Jemson	17,520.00
22	ken	Jemson	16,500.00
23
24	John Johnson	[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE[/URL] !
25	Johnson, John	[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE[/URL] !
Sheet1

Cell Formulas
Range	Formula
B24	=INDEX(C$19:C$22,MATCH(A24,A$19:A$22&" "&B$19:B$22))
B25	=INDEX(C$19:C$22,MATCH(A25,B$19:B$22&", "&A$19:A$22))

Index and Match is returning #VALUE . Could you help me fix formula please?


Regards,

Sean

 

[Jonmo1]

You'll need to enter that formula as an array with CTRL + SHIFT + ENTER

 

[Sean15]

Thanks. I am getting expected and unexpected values. Please see below.

Excel 2010
	A	B	C
18	First Name	Last Name	Amt
19	John	Johnson	15,000.00
20	Smith	Smithson	850.00
21	James	Jemson	17,520.00
22	ken	Jemson	16,500.00
23
24	John Johnson	15,000.00
25	Smith Smithson	850.00
26	James Jemson	#N/A
27	Johnson, John	#N/A
Sheet1

Cell Formulas
Range	Formula
B24	{=INDEX(C$19:C$22,MATCH(A24,A$19:A$22&" "&B$19:B$22))}
B25	{=INDEX(C$19:C$22,MATCH(A25,A$19:A$22&" "&B$19:B$22))}
B26	{=INDEX(C$19:C$22,MATCH(A26,A$19:A$22&" "&B$19:B$22))}
B27	{=INDEX(C$19:C$22,MATCH(A27,B$19:B$22&", "&A$19:A$22))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jonmo1]

It also needs to be an 'Exact Match', the 3rd argument in Match should be 0

=INDEX(C$19:C$22,MATCH(A24,A$19:A$22&" "&B$19:B$22,0))

 

[Sean15]

Thanks, I see how this works now. But I still can't get Johnson, John to work.

Excel 2010
	A	B
27	Johnson, John	#N/A
Sheet1

Cell Formulas
Range	Formula
B27	{=INDEX(C$19:C$22,MATCH(A27,B$19:B$22&", "&A$19:A$22,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jonmo1]

A27 has a comma in it.

 

[Marcílio_Lobão]

Sean15, Good afternoon.

Jonmo1, hi and greetings from Brazil.

Sean15, maybe you can use an anternative formula that can solve both cases with one formula.

Try to use:

ARRAY FORMULA

=IF(ISERROR(SEARCH(",",[B]A24[/B])),INDEX($C$19:$C$22,MATCH([B]A24,[/B] $A$19:$A$22 & " " & $B$19:$B$22, 0)),INDEX($C$19:$C$22;MATCH([B]A24[/B], $B$19:$B$22& ", " & $A$19:$A$22, 0)))

Please, tell us if it worked as you desire.

I hope it helps.

 

[Sean15]

@ Marcílio_Lobão

XL message pops up: formula contains an error.

 

[Marcílio_Lobão]

Sean15,

Excuse-me.
I typed wrongly a SEMICOLON instead a COMMA.

Try to use:

=IF(ISERROR(SEARCH(",",[B]A24[/B])),INDEX($C$19:$C$22,MATCH([B]A24,[/B] $A$19:$A$22 & " " & $B$19:$B$22, 0)),INDEX($C$19:$C$22[SIZE=5][COLOR=#ff0000][B],[/B][/COLOR][/SIZE]MATCH([B]A24[/B], $B$19:$B$22& ", " & $A$19:$A$22, 0)))

I hope it helps.

 

[Sean15]

@ Marcílio_Lobão

Looking good, but I can't figure out why it's bombing on the name Johnson.

Excel 2010
	A	B	C
18	First Name	Last Name	Amt
19	John	Johnson	15,000.00
20	Smith	Smithson	850.00
21	James	Jemson	17,520.00
22	ken	Jemson	16,500.00
23
24	John Johnson	#N/A
25	Smith Smithson	850.00
26	James Jemson	17,520.00
27	Johnson, John	#N/A
28	ken Jemson	16,500.00
Sheet1

Cell Formulas
Range	Formula
B24	{=IF(ISERROR(SEARCH(",",A24)),INDEX($C$19:$C$22,MATCH(A24, $A$19:$A$22 & " " & $B$19:$B$22, 0)),INDEX($C$19:$C$22,MATCH(A24, $B$19:$B$22& ", " & $A$19:$A$22, 0)))}
B25	{=IF(ISERROR(SEARCH(",",A25)),INDEX($C$19:$C$22,MATCH(A25, $A$19:$A$22 & " " & $B$19:$B$22, 0)),INDEX($C$19:$C$22,MATCH(A25, $B$19:$B$22& ", " & $A$19:$A$22, 0)))}
B26	{=IF(ISERROR(SEARCH(",",A26)),INDEX($C$19:$C$22,MATCH(A26, $A$19:$A$22 & " " & $B$19:$B$22, 0)),INDEX($C$19:$C$22,MATCH(A26, $B$19:$B$22& ", " & $A$19:$A$22, 0)))}
B27	{=IF(ISERROR(SEARCH(",",A27)),INDEX($C$19:$C$22,MATCH(A27, $A$19:$A$22 & " " & $B$19:$B$22, 0)),INDEX($C$19:$C$22,MATCH(A27, $B$19:$B$22& ", " & $A$19:$A$22, 0)))}
B28	{=IF(ISERROR(SEARCH(",",A28)),INDEX($C$19:$C$22,MATCH(A28, $A$19:$A$22 & " " & $B$19:$B$22, 0)),INDEX($C$19:$C$22,MATCH(A28, $B$19:$B$22& ", " & $A$19:$A$22, 0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Sean