vlookup formula

lars

Board Regular
Joined
Mar 27, 2002
Messages
105
My lookup is returning #N/A for some reason

It's looking up the first number of a 4 digit number. some may start with a number some may start with a letter. the letter comes back ok but the number doesn't

Here is my formula =vlookup(left(a3,1),ship,2,0)

Thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Ensure that criteria cell and Lookup range
are both formatted text before entering
information.

=VLOOKUP(LEFT(A3,1),Ship,2,0)
This message was edited by Dave Patton on 2002-03-28 19:10
 
Upvote 0
Hi lars

Try:


=VLOOKUP(IF(ISERROR(LEFT(A3)*1),LEFT(A3),LEFT(A3)*1),Ship,2,FALSE)


Try also not to form the habit of using Zeros instead of False, it's bad practice. Later when you try and de-bug you will find it hard to determine whether zeros are really zeros or they represent False. It can also lead to problems if you do an Edit Replace for zeros.
 
Upvote 0
It's looking up the first number of a 4 digit number. some may start with a number some may start with a letter. the letter comes back ok but the number doesn't

Here is my formula =vlookup(left(a3,1),ship,2,0)



=VLOOKUP(IF(ISNUMBER(LEFT(A3)+0),LEFT(A3)+0,LEFT(A3)),ship,2,0)

This assumes that the first column of ship is of mixed data type, that is, consists of numeric and text values.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top