Incrementing cell reference in Lookup formula

M

MartinW

Hi Group,

With the following formula in B10
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A2:AE2),"")

If I input 3 into L8 I need the formula to update to
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A5:AE5),"")

If I input 10 into L8 I need the formula to update to
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A12:AE12),"")
A zero or blank in L8 should leave the formula unchanged.

Thank you for any help
Martin
 
M

Max

Try using INDIRECT, viz.:
=IF(A10=1,LOOKUP(DAY(H$5),Random!A$1:AE$1,INDIRECT("Random!A"&L8+2&":AE"&L8+2)),"")
 
M

MartinW

Thank you Max, Works like a charm!
I had actually attempted that approach but got hopelessly lost in the
syntax.
As usual I was over-complicating things.

Thanks again!
Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

use cell text in lookup function 3
Copy/Fill increments 8
match formula needed 2
Number of days in overlapping date ranges (using array formula?) 7
help with datedif function please 5
Populate cells based on key cell 5
Countif contains a word in a reference cell 3
Multiple Lookup as condition in sumproduct formula 2

Top