vlookup formula

lars · Mar 28th, 2002, 05:41 PM

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

Dave Patton · Mar 28th, 2002, 07:32 PM

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 ]

Dave Hawley · Mar 28th, 2002, 08:25 PM

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.

Yogi Anand · Mar 28th, 2002, 08:34 PM

Good point Dave!
I learnt an additional lesson for no extra charge.

Dave Hawley · Mar 28th, 2002, 08:50 PM

Hi Yogi

If you post you mailing address I'll post the invoice

Aladin Akyurek · Mar 29th, 2002, 01:33 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 05:41 PM	#1
lars Board Regular Join Date: Mar 2002 Location: California Posts: 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

Mar 28th, 2002, 07:32 PM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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 ]

Mar 28th, 2002, 08:25 PM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Mar 28th, 2002, 08:34 PM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Good point Dave! I learnt an additional lesson for no extra charge.

Mar 28th, 2002, 08:50 PM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Yogi If you post you mailing address I'll post the invoice

Mar 29th, 2002, 01:33 AM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	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