VLOOKUP difficulty sorting data?

C

Code Numpty

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 formul
=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceserial2,[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.
 
M

Max

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

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

Code Numpty said:
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(deviceserial2,[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.
Click to expand...
 
C

Code Numpty

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 said:
Presume deviceserial1/2 are single cell named ranges?
Anyway, it could be data inconsistency, as you suspect
Instead of:
=VLOOKUP(deviceserial1, ...
Click to expand...

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

Code Numpty said:
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(deviceserial2,[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.
Click to expand...
Click to expand...
 
M

Max

Maybe try something like this:
=if(isnumber(deviceserial1+0),VLOOKUP(deviceserial1+0,..),
VLOOKUP(deviceserial1,…))
 
C

Code Numpty

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

Max

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:
 
C

Code Numpty

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.
 
C

Code Numpty

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!
 
Top