Lookup the 1st 5 digits

D

Deeds

I am trying to ultimately do a lookup...I will word it out so as to explain
my goal....If the 1st 5 digits of the number in Cell A1 equals the 1st 5
digits of a number in a range....then bring back the corresponding flag from
the range (table) of numbers. I want it to look at a table and find the
number that begins with the same 5 digits and return it's corresponding flag
(column b).
Thanks in advance!
 
D

David Hepner

Try this:

=VLOOKUP(VALUE(LEFT(A1,5)),D1:E9,2,FALSE)

D1:E9 is the range of the table
 
B

Bob Phillips

=IF(ISNA(MATCH(LEFT(c1,5),LEFT(A1:A10,5),0)),"No
match",INDEX(B1:A10,MATCH(LEFT(C1,5),LEFT(A1:A10,5),0)))

which is an array formula so commit with ctrl-shift-enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I

ilanr01

Deeds said:
I am trying to ultimately do a lookup...I will word it out so as to
explain
my goal....If the 1st 5 digits of the number in Cell A1 equals the 1st
5
digits of a number in a range....then bring back the corresponding flag
from
the range (table) of numbers. I want it to look at a table and find
the
number that begins with the same 5 digits and return it's corresponding
flag
(column b).
Thanks in advance!
Click to expand...

Make a range of 3 columns:
The first tour numbers
The second enter this formula: =VALUE(TEXT((LEFT(A1,5)),0))
The third your flags
Now, if your range is in, say, D1:F3, enter this formula inyour check
cell:
=VLOOKUP(VALUE(LEFT(A1,5)),E1:F3,2,FALSE)
Ilan
 
Top