If A:B on UPIN INDEX is sorted in ascending order and can be kept as
such, try:

=IF(LOOKUP(C2,'UPIN INDEX'!$A$2:$A$1500)=C2,LOOKUP(C2,'UPIN
INDEX'!$A$2:$B$1500),"")

Otherwise, try:

=VLOOKUP(C2,'UPIN INDEX'!A:B,2,0)

Jeanette wrote:
I created a lookup formula - LOOKUP(C:C,'UPIN INDEX'!A:A,'UPIN INDEX'!B:B),
the formula works as desired, except when the number being looked up doesn't
exist it doesn't come back with FALSE, it fills the blank with the number and
name just preceding. How do I change this?

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.