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

#### mdavidge

##### Board Regular
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
ABCD
1INIRECT GETSNEED FUNCTION
2A9 BELOWGET RANGE LIKE BELOW
3GROUP#TYPEB9A6:A7
48APPLESPEACHESGRAPES
58PEARSORANGES
610GRAPES
710ORANGES
812BANNANAS
912PEACHES
1014PLUMS
Sheet1

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### just_jon

##### Legend
See the PULL UDF here.

#### Domenic

##### MrExcel MVP
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!

Replies
3
Views
172
Replies
17
Views
981
Replies
33
Views
835
Replies
11
Views
227
Replies
2
Views
125

1,195,936
Messages
6,012,393
Members
441,695
Latest member
MickRobertson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back