vlookup returns list?
Hi,
Try the following formula in B1 of Sheet 1. It assumes that your data start
in Row 1 in Sheet 2, and the user enters the lookup text (e.g., ABC) in A1 of
Sheet 1. Also, type a bogus text (e.g., "END OF DATA") in the cell at Column
A one row below the last row of data in Sheet 2. Modify the range in the
formula (now showing as A1:A101)to suit to your data in Column B of Sheet 2
(include the extra row containing "END OF DATA" in the range)
=IF(COUNTA(Sheet2!$A$1:INDIRECT("Sheet2!$A"&(MATCH ($A$1,Sheet2!$A$1:$A$101,0)+ROW(A1)-1)))=COUNTA(Sheet2!$A$1:INDIRECT("Sheet2!$A$"&(MAT CH($A$1,Sheet2!$A$1:$A$101,0)))),OFFSET(Sheet2!$B1 ,MATCH($A$1,Sheet2!$A$1:$A$101,0)-1,0),"")
Auto-fill the formula down column B to a generous number of rows to
accommodate the maximum possible column B numbers that a lookup text can have.
Regards,
B. R. Ramachandran
"tjb" wrote:
I know there has to be a way to do this.
On Sheet1 the user enters in "ABC" into cell A1. Cell B1 would have
something like a VLOOKUP to look at Sheet2 to fill in the data.
Cell B1:B3 should then fill in with a range of data from a separate list on
Sheet2
Let's say on Sheet2 there is a list of data that looks something like this:
ABC 123
456
789
DEF 012
345
678
I hope this makes at least a little sense to at least one of you out there...
|