VLOOKUP difficulty sorting data?
 

I have 2 versions of a VLOOKUP formula that are giving me problems, which I
fear may be related to the data. Excel 2003 running on XP Pro.

1st formula
=VLOOKUP(deviceserial1,[Calibration_Devices.xls]Sheet1!$A$4:$AM$33,2,FALSE)
2nd formula
=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceserial 2,[Calibration_Devices.xls]Sheet1!$A$4:$AM$33,2,FALSE))

Here is an sample of my data in column 1 of the specified lookup range.
28277
28937
30467
98982
50253098
2000/1C
3500/2C
A2211
A2365
A2432

Are my problems caused by the data being a mixture of alpha/numeric and
other characters? Whatever I set the cell format as doesn't seem to make a
difference. I have tried sorting this column in ascending order.

VLOOKUP difficulty sorting data?
 

Presume deviceserial1/2 are single cell named ranges?
Anyway, it could be data inconsistency, as you suspect
Instead of:
=VLOOKUP(deviceserial1, ...

Try both of these separately, see whether it helps
=VLOOKUP(deviceserial1+0, ...
the "+0" will coerce text number to real number

=VLOOKUP(deviceserial1&"", ...
the &"" bit will make a real number to text number
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Code Numpty" wrote:
I have 2 versions of a VLOOKUP formula that are giving me problems, which I
fear may be related to the data. Excel 2003 running on XP Pro.

1st formula
=VLOOKUP(deviceserial1,[Calibration_Devices.xls]Sheet1!$A$4:$AM$33,2,FALSE)
2nd formula
=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceserial 2,[Calibration_Devices.xls]Sheet1!$A$4:$AM$33,2,FALSE))

Here is an sample of my data in column 1 of the specified lookup range.
28277
28937
30467
98982
50253098
2000/1C
3500/2C
A2211
A2365
A2432

Are my problems caused by the data being a mixture of alpha/numeric and
other characters? Whatever I set the cell format as doesn't seem to make a
difference. I have tried sorting this column in ascending order.

VLOOKUP difficulty sorting data?
 

Thanks Max,
Yes, deviceserial1/2 are single cell named ranges.

Your solution =VLOOKUP(deviceserial1+0, ... Works in part.
When both formulas are amended as you suggest any serial number that is
alphanumeric returns a result.

However, lookups for 2000/1C and 3500/2C returns
#VALUE!.


"Max" wrote:

Presume deviceserial1/2 are single cell named ranges?
Anyway, it could be data inconsistency, as you suspect
Instead of:
=VLOOKUP(deviceserial1, ...

Try both of these separately, see whether it helps
=VLOOKUP(deviceserial1+0, ...
the "+0" will coerce text number to real number

=VLOOKUP(deviceserial1&"", ...
the &"" bit will make a real number to text number
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Code Numpty" wrote:
I have 2 versions of a VLOOKUP formula that are giving me problems, which I
fear may be related to the data. Excel 2003 running on XP Pro.

1st formula
=VLOOKUP(deviceserial1,[Calibration_Devices.xls]Sheet1!$A$4:$AM$33,2,FALSE)
2nd formula
=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceserial 2,[Calibration_Devices.xls]Sheet1!$A$4:$AM$33,2,FALSE))

Here is an sample of my data in column 1 of the specified lookup range.
28277
28937
30467
98982
50253098
2000/1C
3500/2C
A2211
A2365
A2432

Are my problems caused by the data being a mixture of alpha/numeric and
other characters? Whatever I set the cell format as doesn't seem to make a
difference. I have tried sorting this column in ascending order.

VLOOKUP difficulty sorting data?
 

Maybe try something like this:
=if(isnumber(deviceserial1+0),VLOOKUP(deviceserial 1+0,..),
VLOOKUP(deviceserial1,€¦))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Code Numpty" wrote:
Thanks Max,
Yes, deviceserial1/2 are single cell named ranges.

Your solution =VLOOKUP(deviceserial1+0, ... Works in part.
When both formulas are amended as you suggest any serial number that is
alphanumeric returns a result.

However, lookups for 2000/1C and 3500/2C returns
#VALUE!.

VLOOKUP difficulty sorting data?
 

HI Max, I am in a complete muddle now and cannot replicate what was working
befo-(
I am not around tomorrow and will return to it afresh after the weekend.
Thanks for your help.

VLOOKUP difficulty sorting data?
 

Sorry it didn't quite work out fine for you

You might also want to try cleaning "data in column 1 of the specified
lookup range" with something like this, copied down in a helper col to the
right:
=IF(ISNUMBER(A1),A1,TRIM(A1))
with returns then copied and pasted special as values over the lookup col

Believe there are extraneous spaces for these data which are throwing the
matching off:
2000/1C
3500/2C
A2211
A2365
A2432
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Code Numpty" wrote:
HI Max, I am in a complete muddle now and cannot replicate what was working
befo-(
I am not around tomorrow and will return to it afresh after the weekend.
Thanks for your help.

VLOOKUP difficulty sorting data?
 

I couldn't resist a look. Trimming the data and then pasting values means that
VLOOKUP(deviceserial1&"",..................
now works so after the weekend I shall work on nesting the formulas to get
all options in there to do the job. Thanks for your help Max, you've helped
me understand quite a bit about this particular problem.

"Max" wrote:

Sorry it didn't quite work out fine for you

You might also want to try cleaning "data in column 1 of the specified
lookup range" with something like this, copied down in a helper col to the
right:
=IF(ISNUMBER(A1),A1,TRIM(A1))
with returns then copied and pasted special as values over the lookup col

Believe there are extraneous spaces for these data which are throwing the
matching off:
2000/1C
3500/2C
A2211
A2365
A2432
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Code Numpty" wrote:
HI Max, I am in a complete muddle now and cannot replicate what was working
befo-(
I am not around tomorrow and will return to it afresh after the weekend.
Thanks for your help.

VLOOKUP difficulty sorting data?
 

Hi Max, trimming the data and then pasting as values means that with
VLOOKUP(deviceserial1&"",
now works. From this point I think I should be able to put it all together
with nested formulas to get all the options in there. Thanks for your help, I
haven't fallen off the learning curve yet!

"Max" wrote:

Sorry it didn't quite work out fine for you

You might also want to try cleaning "data in column 1 of the specified
lookup range" with something like this, copied down in a helper col to the
right:
=IF(ISNUMBER(A1),A1,TRIM(A1))
with returns then copied and pasted special as values over the lookup col

Believe there are extraneous spaces for these data which are throwing the
matching off:
2000/1C
3500/2C
A2211
A2365
A2432
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Code Numpty" wrote:
HI Max, I am in a complete muddle now and cannot replicate what was working
befo-(
I am not around tomorrow and will return to it afresh after the weekend.
Thanks for your help.

VLOOKUP difficulty sorting data?
 

Welcome, and thanks for posting back
Trust you will get it all up and working ok
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Code Numpty" wrote in message
...
Hi Max, trimming the data and then pasting as values means that with
VLOOKUP(deviceserial1&"",
now works. From this point I think I should be able to put it all together
with nested formulas to get all the options in there. Thanks for your
help, I
haven't fallen off the learning curve yet!