Lookup and Find

[Sean15]

@Aladin Akyurek

We discussed Lookup and Find a while back. Formula returned required values but I just noticed today formula is returning incorrect values when I delete data from row 23.

Row 23 previously had:

23A Johnson,
23B John
23C 18,000.00

Could you advise please?

Excel 2010
	A	B	C	D
19	First Name	Last Name	Amt
20	John	Johnson	15,000.00
21	Smith	Smithson	850.00
22	James	Jemson	17,520.00
23
24	ken	Jemson	16,500.00
25
26	Extract
27	John Johnson		-	should be 15000
28	Smith Smithson		-	should be 850
29	James Jemson		-	should be 17520
30	ken Jemson		16,500.00	should be 16500
31	Ken Jemson		-	should be not found
32	Johnson, John		-	should be not found
Sheet1

Cell Formulas
Range	Formula
C27	=IFERROR(LOOKUP(9.99999999999999E+307,FIND($A$20:$A$24&" "&$B$20:$B$24,$A27&" "),$C$20:$C$24),"Not found")
C28	=IFERROR(LOOKUP(9.99999999999999E+307,FIND($A$20:$A$24&" "&$B$20:$B$24,$A28&" "),$C$20:$C$24),"Not found")
C29	=IFERROR(LOOKUP(9.99999999999999E+307,FIND($A$20:$A$24&" "&$B$20:$B$24,$A29&" "),$C$20:$C$24),"Not found")
C30	=IFERROR(LOOKUP(9.99999999999999E+307,FIND($A$20:$A$24&" "&$B$20:$B$24,$A30&" "),$C$20:$C$24),"Not found")
C31	=IFERROR(LOOKUP(9.99999999999999E+307,FIND($A$20:$A$24&" "&$B$20:$B$24,$A31&" "),$C$20:$C$24),"Not found")
C32	=IFERROR(LOOKUP(9.99999999999999E+307,FIND($A$20:$A$24&" "&$B$20:$B$24,$A32&" "),$C$20:$C$24),"Not found")

 

[Aladin Akyurek]

The issue occurs because search set has a record with empty components...

In C27 control+shift+enter, not just enter, and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,FIND(IF($A$20:$A$24&" "&$B$20:$B$24=" ","##",$A$20:$A$24&" "&$B$20:$B$24),$A27&" "),$C$20:$C$24),"Not found")

 

[Sean15]

Thank you very much.

 

[Aladin Akyurek]

Thank you very much.

You are welcome.