help with vlookup

  • Thread starter Thread starter sokevin
  • Start date Start date
S

sokevin

hi my vlookup forumla is


=VLOOKUP(A17,'ORIGINAL GL'!$A$10:$E$187,4,FALSE)


where column 4 contains data like

HELLO*240203*ABC


however when i do the vlookup only HELLO gets picked up.

how do i do a vlookup such that the entire contents of the cell i
colum 4 gets picked up.


thank
 
I cannot duplicate your problem !

One wild, crazy question.

Is your column wide enough to display the entire returned string of data ?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

hi my vlookup forumla is


=VLOOKUP(A17,'ORIGINAL GL'!$A$10:$E$187,4,FALSE)


where column 4 contains data like

HELLO*240203*ABC


however when i do the vlookup only HELLO gets picked up.

how do i do a vlookup such that the entire contents of the cell in
colum 4 gets picked up.


thanks
 
As long as in Col 4, your HELLO*240203*ABC is actually returning a value (I'm
assuming HELLO and ABC are range names, and that it is actually
=HELLO*240203*ABC in your cell), then that value is what will be returned by the
VLOOKUP statement assuming a match is found on A17. Other than that, it may be
along the lines Ragdyer has suggested, where it is bringing back the whole cell,
ie HELLO*240203*ABC but the column width is only wide enough for HELLO to appear
(You perhaps have something in the next column preventing text spilling over).
 
Do you have multiple values in that first column that match A17? If so, then
only the first match will be returned.


My weird guess:

Since you have wildcards in the 4th column, maybe you have wild cards in the
first column (or wild cards in A17).

If A17 contained something like:

Hi there from *us*

This would match:
Hi there from *us*
Hi there from all of us
hi there from all of us at work

One way around it is to modify your formula:

=VLOOKUP(substitute(A17,"*","~*"),'ORIGINAL GL'!$A$10:$E$187,4,FALSE)

Will fix the problem with "*".

=VLOOKUP(substitute(substitute(substitute(A17,"*","~*"),"?","~?"),"~","~~"),
'ORIGINAL GL'!$A$10:$E$187,4,FALSE)

Will fix the problem with * and ? (both wild cards) and ~ (kind of the escape
character that tells excel that the next charact isn't a wild card).
 
sorry everyone, and thanks for your help.

i was CHOSING the wrong column, i chose column 4 instead of column 5.

column 4 is similar to column5 thats why i did not pick up the error.


anyway a good lesson learnt.


MY TIP: when you got lots of columns, its good to add a row on top o
the column heading and autofill horizontally 1, 2, 3, 4 ,5 6...
.....last column


cheers :
 
Thanks for the feed-back (confession?) -

That's exactly what I do when dealing with 30 column data lists.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

sorry everyone, and thanks for your help.

i was CHOSING the wrong column, i chose column 4 instead of column 5.

column 4 is similar to column5 thats why i did not pick up the error.


anyway a good lesson learnt.


MY TIP: when you got lots of columns, its good to add a row on top of
the column heading and autofill horizontally 1, 2, 3, 4 ,5 6....
.....last column


cheers :)
 
I use something like:

=vlookup(a1,Sheet2!$a$1:$DA$9999,column(AC1)-column(A1)+1,false)

But since this points at the activesheet, I have to be careful.

But if I'm worried, I just highlight:
column(ac1)-column(a1)+1
and hit F9 (to calculate it).

But there's lots of times, the next thing I do is convert to values, so I don't
worry about the sheet references.
 
excellent tip pete

your formula was

=vlookup(a1,Sheet2!$a$1:$DA$9999,column(AC1)-column(A1)+1,false)


is there a quick way of selecting/entering

column(AC1)-column(A1)

in the formula??


thanks
:
 
Under what circumstances does column(AC1)-column(A1)+1 return anything
different from column(AC1)?

Alan Beban
 
My lookup tables don't always start in column A.

I thought I'd be a little more general and show both the references.
 
If your table always begins in column A, you could use Alan's tip of just:
,column(ac1),

but I just type it in or type column( and then point at the cell. (But
sometimes it's quicker just typing than moving your fingers off the keyboard and
to the mouse and back.)
 
Back
Top