hi!

what's wrong with the formula which throws #value error?

the formula is in Sheet1

C1; =VLOOKUP(OR(RIGHT(A1,6),MID(A1,6,6)),sheet2!$A$1:$ C$8,3,FALSE)

the contents of A1:B9 in sheet1 is

0100005500110775159011
0100006000310775159022
0100006000410775159033
0100006000710775159044
0100006001110775159055
010000600160010775159066
010000600200010775159077
010000600200110775159088
010000600160210775159099

the contents of A1:C8 in sheet2 is

055001john 80620840697
060003abraham80620840700
060004joseph 80620840711
060005walker 80620840722
060007nancy 80620840733
060011peter80620840744
060016willy80620840777
060020 mabel 80620840788

any hlp pl?!

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=552440

Try
=VLOOKUP(RIGHT(A1,6),sheet2!$A$1:$C$8,3,FALSE)
or
=VLOOKUP(MID(A1,6,6),sheet2!$A$1:$C$8,3,FALSE)



"via135" wrote:


hi!

what's wrong with the formula which throws #value error?

the formula is in Sheet1

C1; =VLOOKUP(OR(RIGHT(A1,6),MID(A1,6,6)),sheet2!$A$1:$ C$8,3,FALSE)

the contents of A1:B9 in sheet1 is

0100005500110775159011
0100006000310775159022
0100006000410775159033
0100006000710775159044
0100006001110775159055
010000600160010775159066
010000600200010775159077
010000600200110775159088
010000600160210775159099

the contents of A1:C8 in sheet2 is

055001john 80620840697
060003abraham80620840700
060004joseph 80620840711
060005walker 80620840722
060007nancy 80620840733
060011peter80620840744
060016willy80620840777
060020 mabel 80620840788

any hlp pl?!

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=552440

not a hundred percent sure but feel the OR() will not work as the lookup
value in the vlookup
try just

=VLOOKUP(MID(A1,6,6),sheet2!$A$1:$C$8,3,FALSE)
additionally
is column A in sheet 2 text or numbers
one way to verify is to just use the following equation
=mid(sheet1!A1,6,6)=sheet2!A1
if the answer is not "true" the sheet 2 value may be a number formated to
show the initial 0.

"via135" wrote:


hi!

what's wrong with the formula which throws #value error?

the formula is in Sheet1

C1; =VLOOKUP(OR(RIGHT(A1,6),MID(A1,6,6)),sheet2!$A$1:$ C$8,3,FALSE)

the contents of A1:B9 in sheet1 is

0100005500110775159011
0100006000310775159022
0100006000410775159033
0100006000710775159044
0100006001110775159055
010000600160010775159066
010000600200010775159077
010000600200110775159088
010000600160210775159099

the contents of A1:C8 in sheet2 is

055001john 80620840697
060003abraham80620840700
060004joseph 80620840711
060005walker 80620840722
060007nancy 80620840733
060011peter80620840744
060016willy80620840777
060020 mabel 80620840788

any hlp pl?!

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=552440