error using LOOKUP function
 

I am getting a #N/A error when using the LOOKUP function in conjunction with
LEFT.

I have a number followed by a letter (e.g., 7442C). I want to compare the
number portion (7442) with a range of numbers (E1:E10), and return the
corresponding description from F1:F10. To drop off the letter portion, I used
LEFT(B5,LEN(B5)-1).

Here's the formula I'm using:
=LOOKUP(LEFT(B5,LEN(B5)-1),E1:E10,F1:F10)
The result is #N/A

error using LOOKUP function
 

If you are comparing a text string with a number, then you wouldn't expect
to get a match. It might be worth trying --LEFT() to coerce the text string
to a number.
--
David Biddulph

"Horatio J. Bilge, Jr." wrote in
message ...
I am getting a #N/A error when using the LOOKUP function in conjunction
with
LEFT.

I have a number followed by a letter (e.g., 7442C). I want to compare the
number portion (7442) with a range of numbers (E1:E10), and return the
corresponding description from F1:F10. To drop off the letter portion, I
used
LEFT(B5,LEN(B5)-1).

Here's the formula I'm using:
=LOOKUP(LEFT(B5,LEN(B5)-1),E1:E10,F1:F10)
The result is #N/A

error using LOOKUP function
 

First of all if you are looking for an exact match it's better to use
VLOOKUP, then if the
E1:E10 are numbers then you need to convert what you parse with left to
numbers since it is text

=VLOOKUP(--LEFT(B5,LEN(B5)-1),E1:F10,2,0)

you might still get errors but then it might be other causes like extra
spaces etc


--


Regards,


Peo Sjoblom


"Horatio J. Bilge, Jr." wrote in
message ...
I am getting a #N/A error when using the LOOKUP function in conjunction
with
LEFT.

I have a number followed by a letter (e.g., 7442C). I want to compare the
number portion (7442) with a range of numbers (E1:E10), and return the
corresponding description from F1:F10. To drop off the letter portion, I
used
LEFT(B5,LEN(B5)-1).

Here's the formula I'm using:
=LOOKUP(LEFT(B5,LEN(B5)-1),E1:E10,F1:F10)
The result is #N/A

error using LOOKUP function
 

Thank you. That solved it.
~ Horatio

"Peo Sjoblom" wrote:

First of all if you are looking for an exact match it's better to use
VLOOKUP, then if the
E1:E10 are numbers then you need to convert what you parse with left to
numbers since it is text

=VLOOKUP(--LEFT(B5,LEN(B5)-1),E1:F10,2,0)

you might still get errors but then it might be other causes like extra
spaces etc


--


Regards,


Peo Sjoblom


"Horatio J. Bilge, Jr." wrote in
message ...
I am getting a #N/A error when using the LOOKUP function in conjunction
with
LEFT.

I have a number followed by a letter (e.g., 7442C). I want to compare the
number portion (7442) with a range of numbers (E1:E10), and return the
corresponding description from F1:F10. To drop off the letter portion, I
used
LEFT(B5,LEN(B5)-1).

Here's the formula I'm using:
=LOOKUP(LEFT(B5,LEN(B5)-1),E1:E10,F1:F10)
The result is #N/A