Lookup Working TOO Well

[Teddy-B]

Here is a lookup formula I made for the user to enter a unique four digit #
into a spreadsheet (D2) and have correlating data returned (don't worry, I
made a preliminary sort macro for the data list "VOUCH"):

=IF(D2>0,LOOKUP($D2,VOUCH!D$2$5000,VOUCH!C$2:C$5000),"")

The problem is that if and when the user enters a # incorrectly or a # that
is not on the list....Excel automatically looks up the closest match on the
data list and returns THAT-WRONG correlating data.

Is there a way to tell Excel to return a message to the user that the # is
not existent / incorrect? Or is there a way to limit the lookup return to
specific #'s ONLY on the "VOUCH" list?

 

[Eduardo]

Hi,
I assume that you pull your data from Vouch! Column D and your four digit #
is in column C

in E2 enter

=sumproduct(--(D2=Vouch!$c$2:$c$5000),vouch!$d$2:$d$5000)