Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp return several columns?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp return several columns?
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... -- HTH... Jim Thomlinson "General Fear" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp return several columns?
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. General Fear wrote: 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? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp return several columns?
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) "General Fear" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp return several columns?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp return several columns?
General Fear wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup return value from 2 columns | New Users to Excel | |||
VLookup: Return Multiple Columns? | Excel Discussion (Misc queries) | |||
vlookup to return 2 columns | Excel Discussion (Misc queries) | |||
vlookup 3 columns all return same | Excel Discussion (Misc queries) | |||
Can I return multiple columns from a vlookup? | Excel Worksheet Functions |