"ana_15825 - ExcelForums.com" wrote
VLOOKUP won't work. It's a form, not a table, K53 is not nearby

But I seem to be able to get what Aladin suggested to work? <g

In Sheet2, the list below was created in A1:B10
(Numbers in col B were entered as text with a leading apostrophe, following
your specimen data in the orig. post)

301 11517
901 11517
701 40323
801 40323
601 99253
401 41723
501 49325
503 49325
685 49325
662 05498
etc

(Note that col A above need not be sorted as we're going for an exact match
in the VLOOKUP)

In Sheet1,

if we put in say, B2 (can be any cell other than K53):
=IF(K53="","",VLOOKUP(K53,Sheet2!A:B,2,0))

B2 seems to return the correct corresponding text number from col B in
Sheet2 for the input in K53. And if K53 is cleared, a blank: "" will be
returned (Think returning a blank: "" is better than returning a space: " ",
to achieve the same visual effect). If there's no match for the input in
K53, you'll get an #N/A error. If we wanted a blank: "" to be returned for
non-matching inputs in K53 (which could also cover the instance of K53 being
cleared),
we could put instead in B2:

=IF(ISNA(VLOOKUP(K53,Sheet2!A:B,2,0)),"",VLOOKUP(K 53,Sheet2!A:B,2,0))

IMO, the suggested VLOOKUP approach does seem a neater way to do it (we
could simply extend the reference table in Sheet2 to suit future additions,
for example, w/o having to change the formula)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----