Using a lookup to return multiple values in one cell??
Ok, basically I want to do a lookup to return some figures... I know
all the VLOOKUP, etc... trouble is I don't just want to return one figure. It may be 1 figure but then it may be about 9/10 as well. I've got a list of order numbers and a list of orders that need an invoice date next to them. So I've done a bog standard vlookup and it only returns one figure where as there might be 10 invoices related to 1 order... make any sense :? (Table 1) Order Number................... Invoice Date 10010001....................... 22/11/05 10010001....................... 14/10/05 10010002....................... 25/09/05 10010002....................... 21/12/05 10010002....................... 23/11/05 10010002....................... 05/10/05 10010002....................... 15/11/05 10010003....................... 20/12/05 etc... (Table 2) Order Number.... Supplier.... Invoice Date 10010001........ xxxxxxxx.... *lookup from table 1* 10010002........ xxxxxxxx.... *lookup from table 1* 10010003........ xxxxxxxx.... *lookup from table 1* |
Using a lookup to return multiple values in one cell??
Let's say that table 1 is in columns A and B. With the order number of interest entered into cell
D2, this array formula (entered in cell D3 using Ctrl-Shift-Enter): =IF(COUNTIF(A:A,$D$2)=ROW()-ROW($D$2),INDEX($B$1:$B$100, LARGE(($A$1:$A$100=$D$2)*ROW($A$1:$A$100),ROW()-ROW($D$2))),"") copied down for 9 or 10 rows will return all the data from column B associated with the order number in cell D2. HTH, Bernie MS Excel MVP "zim_zimmer" wrote in message oups.com... Ok, basically I want to do a lookup to return some figures... I know all the VLOOKUP, etc... trouble is I don't just want to return one figure. It may be 1 figure but then it may be about 9/10 as well. I've got a list of order numbers and a list of orders that need an invoice date next to them. So I've done a bog standard vlookup and it only returns one figure where as there might be 10 invoices related to 1 order... make any sense :? (Table 1) Order Number................... Invoice Date 10010001....................... 22/11/05 10010001....................... 14/10/05 10010002....................... 25/09/05 10010002....................... 21/12/05 10010002....................... 23/11/05 10010002....................... 05/10/05 10010002....................... 15/11/05 10010003....................... 20/12/05 etc... (Table 2) Order Number.... Supplier.... Invoice Date 10010001........ xxxxxxxx.... *lookup from table 1* 10010002........ xxxxxxxx.... *lookup from table 1* 10010003........ xxxxxxxx.... *lookup from table 1* |
All times are GMT +1. The time now is 03:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com