Lookup the 1st 5 digits
 

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!

Try this:

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

D1:E9 is the range of the table

"Deeds" wrote:

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!

It works.....Thank you very much!

"David Hepner" wrote:

Try this:

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

D1:E9 is the range of the table

"Deeds" wrote:

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!

=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)


"Deeds" wrote in message
...
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!

Deeds Wrote:
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!

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


--
ilanr01
------------------------------------------------------------------------
ilanr01's Profile: http://www.excelforum.com/member.php...o&userid=26797
View this thread: http://www.excelforum.com/showthread...hreadid=401156