lookup error!

[via135]

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

01000055001>>>>>10775159011
01000060003>>>>>10775159022
01000060004>>>>>10775159033
01000060007>>>>>10775159044
01000060011>>>>>10775159055
0100006001600>>>10775159066
0100006002000>>>10775159077
0100006002001>>>10775159088
0100006001602>>>10775159099

the contents of A1:C8 in sheet2 is

055001>>john>>> 80620840697
060003>>abraham>80620840700
060004>>joseph>> 80620840711
060005>>walker>> 80620840722
060007>>nancy>> 80620840733
060011>>peter>>>80620840744
060016>>willy>>>>80620840777
060020 mabel 80620840788

any hlp pl?!

-via135

[Barb Reinhardt]

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
>
> 01000055001>>>>>10775159011
> 01000060003>>>>>10775159022
> 01000060004>>>>>10775159033
> 01000060007>>>>>10775159044
> 01000060011>>>>>10775159055
> 0100006001600>>>10775159066
> 0100006002000>>>10775159077
> 0100006002001>>>10775159088
> 0100006001602>>>10775159099
>
> the contents of A1:C8 in sheet2 is
>
> 055001>>john>>> 80620840697
> 060003>>abraham>80620840700
> 060004>>joseph>> 80620840711
> 060005>>walker>> 80620840722
> 060007>>nancy>> 80620840733
> 060011>>peter>>>80620840744
> 060016>>willy>>>>80620840777
> 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
>
>

[bj]

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
>
> 01000055001>>>>>10775159011
> 01000060003>>>>>10775159022
> 01000060004>>>>>10775159033
> 01000060007>>>>>10775159044
> 01000060011>>>>>10775159055
> 0100006001600>>>10775159066
> 0100006002000>>>10775159077
> 0100006002001>>>10775159088
> 0100006001602>>>10775159099
>
> the contents of A1:C8 in sheet2 is
>
> 055001>>john>>> 80620840697
> 060003>>abraham>80620840700
> 060004>>joseph>> 80620840711
> 060005>>walker>> 80620840722
> 060007>>nancy>> 80620840733
> 060011>>peter>>>80620840744
> 060016>>willy>>>>80620840777
> 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
>
>