VLookup help please!

  • Thread starter Thread starter bimmerman
  • Start date Start date
B

bimmerman

OMFG I am about to strangle someone!!! I cannot for the life of m
figure out why this simple formula will not work. Can someone pleas
help me out here.

What I'm trying to do is search for the value in column D and see i
it's in column L. If it is, return the value in column M. In m
example, the highlighted cell should be returning 60.

And I tried changing it to 'true' and removing that variabl
completely, same thing.

I formatted all the cells to 'general' also so they are the same. M
real sheet is much bigger, I just made these as a reference.

Here's the tricky thing, if I do a simple 'find' on the value in th
lookup column, it finds it, but won't return that 2nd value. If
copy/paste the value from D: into L:, it will return the value???

Can someone please help me before I seriously lose it here??


[image: http://www.arekahc.com/screw_excel.jpg
 
It appears the numbers in column L are actually text
because they're left-justified. Convert these to actual
numbers by keying a 1 into an open cell (make sure that 1
right justifies), copy that cell, select your range of
numbers in col. L, paste special > multiply.

HTH
Jason
Atlanta, GA
 
Or if there is some reason they need to be text, change the A1 reference in the
VLOOKUP formula for TEXT(D5,"@")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Jason Morin said:
It appears the numbers in column L are actually text
because they're left-justified. Convert these to actual
numbers by keying a 1 into an open cell (make sure that 1
right justifies), copy that cell, select your range of
numbers in col. L, paste special > multiply.

HTH
Jason
Atlanta, GA
-----Original Message-----

OMFG I am about to strangle someone!!! I cannot for the life of me
figure out why this simple formula will not work. Can someone please
help me out here.

What I'm trying to do is search for the value in column D and see if
it's in column L. If it is, return the value in column M. In my
example, the highlighted cell should be returning 60.

And I tried changing it to 'true' and removing that variable
completely, same thing.

I formatted all the cells to 'general' also so they are the same. My
real sheet is much bigger, I just made these as a reference.

Here's the tricky thing, if I do a simple 'find' on the value in the
lookup column, it finds it, but won't return that 2nd value. If I
copy/paste the value from D: into L:, it will return the value???

Can someone please help me before I seriously lose it here??


[image: http://www.arekahc.com/screw_excel.jpg]


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
Click to expand...
 
Doh!!! read D5 for A1 :-(

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Ken Wright said:
Or if there is some reason they need to be text, change the A1 reference in the
VLOOKUP formula for TEXT(D5,"@")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Jason Morin said:
It appears the numbers in column L are actually text
because they're left-justified. Convert these to actual
numbers by keying a 1 into an open cell (make sure that 1
right justifies), copy that cell, select your range of
numbers in col. L, paste special > multiply.

HTH
Jason
Atlanta, GA
-----Original Message-----

OMFG I am about to strangle someone!!! I cannot for the life of me
figure out why this simple formula will not work. Can someone please
help me out here.

What I'm trying to do is search for the value in column D and see if
it's in column L. If it is, return the value in column M. In my
example, the highlighted cell should be returning 60.

And I tried changing it to 'true' and removing that variable
completely, same thing.

I formatted all the cells to 'general' also so they are the same. My
real sheet is much bigger, I just made these as a reference.

Here's the tricky thing, if I do a simple 'find' on the value in the
lookup column, it finds it, but won't return that 2nd value. If I
copy/paste the value from D: into L:, it will return the value???

Can someone please help me before I seriously lose it here??


[image: http://www.arekahc.com/screw_excel.jpg]


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...
Click to expand...
Click to expand...
 
From the picture it looks as if the values in
L are text (left aligned is text unless you change it yourself)

try this amendment

=VLOOKUP(TEXT(D5,"General"),L:M,2,0)

or better make sure the values in L are numbers, copy an empty cell,
select L and do edit>paste special and check add. If that doesn't work you
have other
invisible characters (maybe you copied it from the web?).

=VLOOKUP(TEXT(D5,"General"),TRIM(SUBSTITUTE(L5:M20,CHAR(160),"")),2,0)

entered with ctrl + shift & enter

however if that's the case you should use you original formula and then run
David McRitchie's trimall macro

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Thanks for all the help everyone. I did get it to work, by accident
think. I copied the values into textpad and when I pasted them bac
into excel, the leading zeros were gone. I did that to all the column
and the formula worked!

Thanks
 
The value you're looking up is a number, the value in the table is text.

Alan Beban

=VLOOKUP(D2,L:M,2,FALSE)
-----Original Message-----

OMFG I am about to strangle someone!!! I cannot for the
Click to expand...

life of me
figure out why this simple formula will not work. Can
Click to expand...

someone please
help me out here.

What I'm trying to do is search for the value in column D
Click to expand...

and see if
it's in column L. If it is, return the value in column M.
Click to expand...

In my
example, the highlighted cell should be returning 60.

And I tried changing it to 'true' and removing that
variable

completely, same thing.

I formatted all the cells to 'general' also so they are
Click to expand...

the same. My
real sheet is much bigger, I just made these as a
reference.

Here's the tricky thing, if I do a simple 'find' on the
Click to expand...

value in the
lookup column, it finds it, but won't return that 2nd
Click to expand...

value. If I
copy/paste the value from D: into L:, it will return the
value???

Can someone please help me before I seriously lose it
here??


[image: http://www.arekahc.com/screw_excel.jpg]


------------------------------------------------

~~View and post usenet messages directly from
http://www.ExcelForum.com/

.
Click to expand...
Click to expand...
 
Back
Top