Help with LOOKUP and SEARCH

[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 A Johnson	15,000.00
25	Smith Smithson	850.00
26	Johnson, John	15,000.00
Sheet1

Cell Formulas
Range	Formula
B24	=LOOKUP(9.99999999999999E+307,SEARCH($A$19:$A$22,A24)*SEARCH($B$19:$B$22,A24),$C$19:$C$22)
B25	=LOOKUP(9.99999999999999E+307,SEARCH($A$19:$A$22,A25)*SEARCH($B$19:$B$22,A25),$C$19:$C$22)
B26	=LOOKUP(9.99999999999999E+307,SEARCH($A$19:$A$22,A26)*SEARCH($B$19:$B$22,A26),$C$19:$C$22)

LOOKUP and SEARCH formula is adapted from one of Aladin Akyurek's post.

Why is formula in B24 and B26 returning $15,000.00 when search criteria does not match?

Could someone post formula for exact match please.

 

[Aladin Akyurek]

What should be the results?

 

[Sean15]

Since John A Johnson is not in search range, I thought formula would return #N/A or are my thoughts wrong?

 

[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	Johnson,	John	18,000.00
23	ken	Jemson	16,500.00
24
25	Extract
26	John	Johnson	15,000.00
27	Johnson	John	-
28	Johnson,	John	18,000.00
29	ken	Jemson	16,500.00
30	Ken	Jemson	-
Sheet1

Cell Formulas
Range	Formula
C26	=SUMPRODUCT((EXACT($A$19:$A$23,A26)*(EXACT($B$19:$B$23,B26)*($C$19:$C$23))))
C27	=SUMPRODUCT((EXACT($A$19:$A$23,A27)*(EXACT($B$19:$B$23,B27)*($C$19:$C$23))))
C28	=SUMPRODUCT((EXACT($A$19:$A$23,A28)*(EXACT($B$19:$B$23,B28)*($C$19:$C$23))))
C29	=SUMPRODUCT((EXACT($A$19:$A$23,A29)*(EXACT($B$19:$B$23,B29)*($C$19:$C$23))))
C30	=SUMPRODUCT((EXACT($A$19:$A$23,A30)*(EXACT($B$19:$B$23,B30)*($C$19:$C$23))))

Hi:

I ended up with this. A bit crude but it returns exact matches.

Adapted formula from here:

https://www.ablebits.com/office-addins-blog/2014/08/19/vlookup-case-sensitive-excel/

 

[Aladin Akyurek]

In C26 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,FIND($A$19:$A$23&" "&$B$19:$B$23,$A26&" "&$B26),$C$19:$C$23),"-")

 

[Sean15]

@Aladin Akyurek

Magical. Thank you very much.

How can I remove embarrassing post #4?

 

[Aladin Akyurek]

@Aladin Akyurek

Magical. Thank you very much.

How can I remove embarrassing post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4 ?[/URL]

You are welcome. There is nothing embarrassing about post #4 . By the way, it shows a possible option.