Functio like INDIRECT, but delever RANGE of value from text?

[mdavidge]

Hello,

In the sheet below, I can use INDIRECT to return the cell value of B9 from the text "B9" in cell C3. Is there a formula that allows for returning a RANGE of values based text value of RANGE like A6:A7 as in the sample below?

Any help or tricks are much appreciated.

Thanks,
Mike

Book1
	A	B	C	D
1			INIRECT GETS	NEED FUNCTION
2			A9 BELOW	GET RANGE LIKE BELOW
3	GROUP#	TYPE	B9	A6:A7
4	8	APPLES	PEACHES	GRAPES
5	8	PEARS		ORANGES
6	10	GRAPES
7	10	ORANGES
8	12	BANNANAS
9	12	PEACHES
10	14	PLUMS
Sheet1

 

[just_jon]

See the PULL UDF here.

 

[Domenic]

Try the following...

1) Select/highlight D4:D10

2) Enter the following formula:

=IF(D3<>"",IF(ROW()-ROW(D4)+1<=ROWS(INDIRECT(D3)),INDIRECT(D3),""),"")

3) Confirm with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!