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?

&3&40&&&	1
&7&11&25&43&	2
&22&29&55&61&	3
&201&237&238&239&	4
&15&49&205&&	5
&33&64&&&	6
&250&254&&&	7
&&&&&	8
&&&&&	9
&1&&&&	10
&200&&&&	11
&86&&&&	12
&80&&&&	13
&84&&&&	14
&100&110&150&160&	15
&102&112&152&162&	16
&103&113&153&163&	17
&101&111&151&161&	18
&350&&&&	19
&905&912&913&&	20
&&&&&	21
&901&&&&	22
&915&&&&	23
&902&&&&	24
&916&&&&	25
&903&&&&	26
&918&&&&	27

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[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!