vLookUp return several columns?

[General Fear]

Vlookup only returns one column that matches the value searched. I
wanted to return several columns, otherwise, I am forced to use
vlookup for each column.

Below is an example

Customer ID, Customer Name, Address1, Address2, City, State, Zip


If I match on Customer ID, then I want name, address, city, state zip
in one shot.

Is this possible?

 

[Jim Thomlinson]

Functions such as Sum or Vlookup or... return single values. So the long and
the short of it is that you are goind to end up wit multiple lookups. Using
index and match you can make a formula that you can drag so that you do not
need to increment the offset number manually if that is of interest to you.
Index match is a much better formula than a Vlookup as it is far less prone
to returning a wrong result...

 

[Dave Peterson]

You could fiddle with multicelled array formula--or use multiple =vlookup()'s.

But I've found that the quickest way is to use a column to return the row of the
match and then use that in other cells in the other columns that retrieves the
data.

=match(a2,sheet2!a:a,0)
(say this is in G2)

This will return an error if there is not a match. It will return the number of
the first row of the matching cell if there is a match.

Then in the subsequent columns, I could use this in H2:
=if(iserror($g2),"",index(sheet2!b:b,$g2))
and drag to the right to return the neighboring cell values.

Using tons and tons of =vlookup()'s (against giant tables) will slow down excel
each time they need to be calculated.

 

[Chip Pearson]

You can do this with the OFFSET function in an array formula. If you
customer IDs are in A1:A4, the array formula
\
=OFFSET(A1,MATCH("b",A1:A4,0)-1,0,1,3)

will return the values from columns B, C, and D for the row in which A
equals "b". To enter this, select the cells that are to contain the
results, type in the formula and press CTRL SHIFT ENTER rather than just
ENTER. If you do not press CTRL SHIFT ENTER, the formula will not work
properly.

See http://www.cpearson.com/Excel/ArrayFormulas.aspx for more information
about array formula.



--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ward376]

Sounds more like a query than a lookup. You can use MS Query to do
what you described.

Make sure the file you're extracting from is closed or you'll
experience a memory leak.

Cliff Edwards

 

[Alan Beban]

Vlookup only returns one column that matches the value searched. I
wanted to return several columns, otherwise, I am forced to use
vlookup for each column.

Below is an example

Customer ID, Customer Name, Address1, Address2, City, State, Zip


If I match on Customer ID, then I want name, address, city, state zip
in one shot.

Is this possible?

=VLookup(lookup_value, lookup_array, {2,3,4,5,6,7}) array entered.

Alan Beban