# using indirect references

#### johngio

##### Board Regular
hi all,

I'm trying to use MATCH across a range of figures, and I want the first row number to be that which is in another cell (the number 270 at the moment, but it will change). Here is my formula:

=MATCH(0,'Sheet1!BY(INDIRECT("\$Q\$59")):BY4000, 0)

I'm trying to find the first zero after the series of numbers (which works fine when I enter 270 instead of (INDIRECT("\$Q\$59")). I'm pretty sure my layout is wrong.

I keep getting a #NAME? error.

Can someone please tell me the correct way to lay this out?

Thanks

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
If any one has any idea; please let me know; even if it's a different formula - I have a cell that will generate a number, then I need to put that number into an array such as A 'the number I will generate' :A4000

Thanks

Hi John,

Try: =MATCH(0,INDIRECT("Sheet1!BY"&\$Q\$59):INDIRECT("Sheet1!BY4000"),0)

This assumes your reference cell Q59 is on Sheet2

If 270 stands for row 270...

=MATCH(0,INDEX(Sheet1!\$BY:\$BY,\$O\$59):Sheet1!\$BY\$4000,0)

Hooray!!!!

You guys are legends!!!!

Replies
7
Views
261
Replies
1
Views
219
Replies
6
Views
404
Replies
12
Views
264
Replies
4
Views
273

1,202,913
Messages
6,052,530
Members
444,589
Latest member
Ben AFF

### 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