vlookup returns list?

T

tjb

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...
 
B

Biff

Hi!

As long as the number of values to return for each lookup_value are the
same, 3:

=INDEX(Sheet2!B$1:B$20,MATCH(A$1,Sheet2!A$1:A$20,0)+ROWS($1:1)-1,0)

Copy down to B3.

Biff
 
T

tjb

what if the number of values are different?

Biff said:
Hi!

As long as the number of values to return for each lookup_value are the
same, 3:

=INDEX(Sheet2!B$1:B$20,MATCH(A$1,Sheet2!A$1:A$20,0)+ROWS($1:1)-1,0)

Copy down to B3.

Biff
Click to expand...
 
B

B. R.Ramachandran

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$"&(MATCH($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
 
B

Biff

what if the number of values are different?

I had a feeling that would be the case!

Then it gets real complicated!

See B. R. Ramachandran's reply.

Biff
 
B

Biff

Hi!

Your formula works as long as the source table starts in row 1. If the
source table starts in any other row then the references are all out of
sequence.

Here's a modification based on your formula that's more flexible. Assumes
the source table is in the range Sheet2A5:B17:

Array entered:

=IF(COUNTA(INDIRECT("Sheet2!A"&MAX((Sheet2!A$5:A$17=A$1)*(ROW(A$5:A$17)))&":A"&MAX((Sheet2!A$5:A$17=A$1)*(ROW(A$5:A$17)))+ROWS($1:1)-1))=1,OFFSET(Sheet2!A$1,MATCH(A$1,Sheet2!A:A,0)-1+ROWS($1:1)-1,1),"")

Biff
 
Top