VLOOKUP - want to return multiple lines of data versus 1 cell

[Spartandiver]

When searching for Corvette, I want VLOOKUP to return all of the data t
the right of the word Corvette. Traditional VLOOKUP formulas ar
designed to only return 1 data value say " 2" cells to the right o
however many to the right - but still only 1 cell is returned. I wan
to return all 5 values to the right of the cell with the wor
"Corvette".

If Corvette is in cell A1. I want the data in cell A2, A3, A4, A5, A
to be returned and entered into their own cells. I think it involve
Columns(), but have no idea how it's used. Thank you

 

[Frank Kabel]

Hi
in this case you may use the following on a second sheet. Lets assume
in A1 is the word to lookup. use the following in B1
=VLOOKUP($A1,'sheet1'!$A$1:$G$100,COLUMN(),0)
and copy this formula to the right

 

[A.W.J. Ales]

Hi Frank, Spartandiver,

If the number of Lookups is large or the area you search in is, it might be
more efficient to use MATCH() to find the rownumber (within the searcharea),
combined with INDEX(SearchArea,rownr, columnr) to retrieve the data.
See my response to Fred Smith (16-4-2004; Find fastes way to do lookups) in
microsoft.public.excel.programming

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Alan Beban]

I assume you mean the data in B1, C1, D1, E1 and F1 to be returned since
those are the cells to the right of A1.

=VLOOKUP("Corvette",A1:F100,{2,3,4,5,6},FALSE) array entered into a
5-column row of cells.

Alan Beban

 

[Spartandiver]

Alan, Auk, Frank,
Thanks for the replies. Still no luck though. However, I haven'
tried Auk's method yet.

The other 2 methods do return a value, but it's only 1 value and onl
in the same cell that the formula is in. Maybe my expectation isn'
grounded. But I'd like to select "Corvette" or "Camaro" or whateve
from a validation list, and then several (25-30 cells) worth of dat
would instantly be populated. Does each cell need to have thi
formula? Maybe that is where I'm totally missing the boat. I wil
then manipulate the updated data - so maybe a copy/past special a
value is required as well

 

[Frank Kabel]

Hi
each cell has to contain a formula. A formula can only return a value
but does not change other cells.

 

[RagDyer]

Hey Spartandiver,

I can see by your answer that you didn't fully understand Alan's suggested
solution.
Yes ... you're correct in guessing that there must be a formula in each cell
to return your data from the different columns ... BUT ... Alan's
suggestion, if followed *correctly*, will place a formula in each of the
necessary cells with a simple selection of the needed number of columns and
a CSE (<Ctrl> <Shift> <Enter)!!!

Maybe you'll understand if I rephrase it for you.

Say your data list is A10:F100

You will enter the vehicle name you're looking up into A1.

Click in B1 and drag the selection to F1.
The focus is in B1 (it's uncolored).

Enter this formula:

=VLOOKUP(A1,A10:F100,{2,3,4,5,6},0)

NOW hit CSE (<Ctrl> <Shift> <Enter>.

You now have the formulas entered in the five columns and all your data is
displayed.

--

HTH,

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

message Alan, Auk, Frank,
Thanks for the replies. Still no luck though. However, I haven't
tried Auk's method yet.

The other 2 methods do return a value, but it's only 1 value and only
in the same cell that the formula is in. Maybe my expectation isn't
grounded. But I'd like to select "Corvette" or "Camaro" or whatever
from a validation list, and then several (25-30 cells) worth of data
would instantly be populated. Does each cell need to have this
formula? Maybe that is where I'm totally missing the boat. I will
then manipulate the updated data - so maybe a copy/past special as
value is required as well.

 

[Spartandiver]

EXCELLENT WORK!

You guys/girls rule! I just cut & pasted you're fomula, and with
little range changes it worked like a charm.

Now please help me understand the formula. In the formula

=VLOOKUP(A1,A10:F100,{2,3,4,5,6},0)

what does the ",0" represent? Maybe a false statement would work a
well.

Also, instead of {2,3,4,5,6} is there a shortcut method that'll work a
in {B2:G2}? Or must I use {2,3,4,5,6}.

Again, I tip my hat..

 

[Frank Kabel]

Hi
the zero as last parameter could be replaced by False (FALSE = 0 / TRUE
= 1). It's just faster to write

For the second question: Yes you must use {2,3,4,5,6}. This indicates
the column index: Defines, from which column of the lookup range the
value should be returned (the 2nd, the 3rd, etc. column)

 

[Dave Peterson]

This'll work, too:

=VLOOKUP(A1,A10:F100,COLUMN(B1:F1),0)

 

[RagDyeR]

Don't think I've seen that one before Dave!

It even works without the row designations
Column(B:F)

 

[Dave Peterson]

But in either case, you have to be careful not to insert/delete columns (or
delete row 1 in my example).

 

[RagDyeR]

Not really.
Delete *either* column B, C, D, E, or F, and see that your version (B:F) is
more stable then the array constant ({2,3,4,5,6}) version.

The B:F losses *one* column, while {2,3,4,5,6}losses the entire array.

 

[Dave Peterson]

It might be more stable than the array constant. But it still scares me.

I very rarely have a lookup table on the same sheet and if my column(B:F)
referred to the sheet with the formula--not the sheet with the table, then I
don't like it.

I think almost all (slight exaggeration) the vlookup's I do get converted to
values (so I can share the smaller workbook).

But if I wanted to keep the formulas:
=VLOOKUP(A1,Sheet2!$A:$F,COLUMN(Sheet2!B:F),FALSE)
might be better.

(But since I usually convert to values, I can live with the threat of deleting
columns on the wrong sheet!)

On the other hand, if I really wanted to return 5 columns, I think I'd use one
column to see if there was a match, then a series of index()'s based on that
other column.

=match(a1,sheet2!a:a,0)

=if(iserror(thatcell),"",index(sheet2!b:b,thatcell))

=====
I haven't done any timing studies, but it sure looks like the array entered
vlookup still calculates multiple times--just like multiple vlookup's.

When I do this with lots of data, it sure looks like it takes the same time as 5
vlookups.

The main benefit that I see is that I only type the formula once.

 

[Spartandiver]

1) What would prompt a #REF or #NA response error? I'm trying to us
the column() in the VLOOKUP function. I can only get the function t
work properly when I enter it as this {2,3,4,5,6}, and want it to wor
with this coulmn(B:F)

2) Is there a way I can have the VLOOKUP skip a cell? For example
{2,3,4,5} now inputs data into B1,C1,D1,E1. I'd like it to enter i
into B1,C1,E1,F1 (nothing in D1). What do you think

 

[Alan Beban]

{2,3,5,6} works fine; I'm surprised you didn't just try it instead of
posting.

Alan Beban

 

[Dave Peterson]

I can get #ref errors when I try to bring back a column that isn't in the lookup
table.

For instance:

=vlookup(a1,sheet2!a:e,6,false)

A:E is only 5 columns and I'm trying to bring back the 6th.

And if there's no match between the lookup value and the first column of the
lookup table, you'll get #n/a.

One way to hide that error:

=if(iserror(vlookup(a1,sheet2!a:e,3,false)),"Missing",
vlookup(a1,sheet2!a:e,3,false))

(replace Missing with what you want.)

But for your array formula:
=VLOOKUP(A1,A10:F100,COLUMN(B1:F1),0)
maybe:
=IF(ISERROR(MATCH(A1,A10:A100,0)),"missing",VLOOKUP(A1,A10:F100,COLUMN(B:F),0))

And I read #2 slightly differently than Alan. I read it that you want to skip a
column in the resulting vlookup(), not the table.

I don't think so. You could enter two similar array formulas, though. One in
B1:C1 and one in E1:F1.

 

[Spartandiver]

Ah, that was my problem. Dave/Alan thanks for the help.

Dave, yes I was trying to skip a cell in the array. The column tric
is great since there will actually be 20 different cells of data.
understand that I'll just have to re-enter the formula after tha
skipped cell.

No offense, but I hope we don't talk again in the near future.: