VLOOKUP mutiple column return?
BobG
=VLOOKUP(F20,$U$100:$AB$902,4,FALSE)
I'd like to find out how to change the formula to return text data from 3
columns to a cell. I.E City( column), ST (column 9) & Zip code(column 10).
Like this...but this does not work.
=VLOOKUP(F20,$U$100:$AB$902,8,&" "&,9,&" "&,10,FALSE)
Thanks
Bob
CLR
This might be what you're looking for......
=VLOOKUP(A1,myrange,2,false)&VLOOKUP(A1,myrange,3,false)&VLOOKUP(A1,myrange,
4,false)
Vaya con Dios,
Chuck, CABGx3
"BobG" <r.g...@attbi.com> wrote in message
news:CPDW9.1443$4u5...@rwcrnsc52.ops.asp.att.net...
Norman Harker
You need to concatenate your VLOOKUPS:
Use:
=VLOOKUP(F20,$U$100:$AB$902,8,FALSE) & " " &
VLOOKUP(F20,$U$100:$AB$902,9,FALSE) & " " &
VLOOKUP(F20,$U$100:$AB$902,10,FALSE)
It should work but without reconstructing your workbook, you'll need to
test.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"BobG" <r.g...@attbi.com> wrote in message
news:CPDW9.1443$4u5...@rwcrnsc52.ops.asp.att.net...
L. Howard Kittle
Another way is to select three cells in a row and while still selected,
enter this formula in the formula bar. Now do an array enter
(Ctrl-Alt-Enter).
=VLOOKUP(F20,$U$100:$AB$902,{8,9,10},FALSE)
This will put the data in three different cells. May be useful for sorting
by city or zip.
HTH
Regards,
Howard
"BobG" <r.g...@attbi.com> wrote in message
news:CPDW9.1443$4u5...@rwcrnsc52.ops.asp.att.net...
BobG
9 and a "space" between 9 & 10.
I just can't seem to get these formulas
Bob
"CLR" <crob...@tampabay.rr.com> wrote in message
news:u2JDo3$vCHA.2000@TK2MSFTNGP11...
CLR
Hi Bob.....
=VLOOKUP(A1,myrange,2,false)&","&VLOOKUP(A1,myrange,3,false)&"
"&VLOOKUP(A1,myrange,4,false)
see the difference?.......if you want to add a comma, or a space, you just
have to enclose them between two quotation marks and then add another & sign
and continue with the formula.......
Vaya con Dios,
Chuck, CABGx3
"BobG" <r.g...@attbi.com> wrote in message
news:JxGW9.53615$Yq3.11465@sccrnsc02...
BobG
Tried your suggestion...It doesn't seem to work. The first one you gave me
does, but all the test is jointed together. But the second one with the [
&","& and &" "& ] does not.
Any ideas?
Bob G
"CLR" <crob...@tampabay.rr.com> wrote in message
news:Op3fJoBwCHA.440@TK2MSFTNGP12...
CLR
I rechecked my copy of the formula and it still seems to work here.........a
couple of things might be the problem.......one is, it's a long formula, and
may have "word-wrapped" during the email transmission.......if you copied
and pasted it into your sheet, check and be sure you have all the correct
stuff in the same cell..........the other thing is, if you converted the
term "myrange" to your actual range as sent in your email ($U$100:$AB$902),
then that range does not support the 8,9, and 10 offsetting you
want.......if you really want 8,9,and 10 offsetting, then you must extend
your range to at least column AD........check these things, and if they
aren't it, and if you want......you can email me a copy of your worksheet to
my home address, ( crob...@tampabay.rr.com ) , don't send your worksheet to
the newsgroup, and I'll see if I can find out whats happening.
Vaya con Dios,
Chuck, CABGx3
"BobG" <r.g...@attbi.com> wrote in message
news:IjTW9.63058$hl1.6699@sccrnsc04...
BobG
closing the WB and putting it in different cells..it works.
Sorry for the repeat questions about a modified formula you helped me build
that worked most everywhere but on my WS.
All is well......... (for now).
Many Thanks, again.
Bob G
"CLR" <crob...@tampabay.rr.com> wrote in message
news:ubO1OGOwCHA.2124@TK2MSFTNGP11...
CLR
Vaya con Dios,
Chuck, CABGx3
"BobG" <r.g...@attbi.com> wrote in message
news:wtdX9.76600$kH3.11659@sccrnsc03...