MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula - Generate Range Reference from Lookup?


Posted by JAF on January 24, 2001 6:41 AM

Hiya

Bit of a weird one this, but here goes.

A1:A4 contain the value Main
A5:A6 contain the value Finance
B1 = Main1, B2 = Main2, B3 = Main3, B4 = Main4, B5 = Fin1, B6 = Fin2
Column C contains the unique division names (C1 = Main, C2 = Finance)

What I would like to do is to create a formula (in cells D1 and D2) that returns the range reference of teh entries in Column B where the entry in Column A is equal to the value in Column C.

in other words D1 should return the result $B$1:$B$4 and D2 should return $B$5:$B$6 - those being the ranges of departments belonging to the each Division (as outlined in Column A) that match the values in cells C1 and C2.

There you go - clear as mud!

I'd prefer to do this in a formula rather than populating it by running a macro if at all possible. I have worked out some code that will do the job, but it's a bit messy and a formula seems the obvious way to go.

I've tried the CELL and ADDRESS functions but not got the results I needed.

Any suggestions?


JAF


Posted by Mark W. on January 24, 2001 7:26 AM

=CELL("address",OFFSET(INDEX($A$1:$A$6,MATCH(C1,$A$1:$A$6,0)),,1))&":"&CELL("address",OFFSET(INDEX($A$1:$A$6,MATCH(C1,$A$1:$A$6,0)),COUNTIF($A$1:$A$6,C1)-1,1))

Posted by JAF on January 24, 2001 7:52 AM

Fantastic!

Absolutely superb.

I was on the right lines with CELL("address" but hadn't used INDEX and MATCH.

Thanks for your help.

JAF