Excel Vlookup Issue

[SimonVOwen]

Hello All,

I am searching for a value with a vlookup that can range from 0-999.
Occasionally the value will not exist so I am returning a value of 1.

=IFERROR(VLOOKUP("*"&M12&"*",EPN!$Y$14:$Z$40,2,FALSE),1)

The cells I am referencing are as below for the 2 fields.
However I am getting false results.
When searching for 50 that does not exist it is returning the value of 7 as there is a 250 there.
Is there anyway to code around this to make sure it does not reference incomplete numbers?
[TABLE="width: 225"]
<tbody>[TR]
[TD]&3&40&&&[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]&7&11&25&43&[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]&22&29&55&61&[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]&201&237&238&239&[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]&15&49&205&&[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]&33&64&&&[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]&250&254&&&[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]&&&&&[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]&&&&&[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]&1&&&&[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]&200&&&&[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]&86&&&&[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]&80&&&&[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]&84&&&&[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]&100&110&150&160&[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]&102&112&152&162&[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]&103&113&153&163&[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]&101&111&151&161&[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]&350&&&&[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]&905&912&913&&[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]&&&&&[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]&901&&&&[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]&915&&&&[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]&902&&&&[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]&916&&&&[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]&903&&&&[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD]&918&&&&[/TD]
[TD="align: right"]27[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Eric W]

Try:

=IFERROR(VLOOKUP("*&"&M12&"&*",EPN!$Y$14:$Z$40,2,FALSE),1)

 

[SimonVOwen]

Cheers simple solution that works, just as I like it.. shame I didn't spot it haha!

 

[Eric W]

Glad to help!