Function to Return Range of matches...

[SteveMG]

Hi, I've got some data in a sheet as below

Please Login or Register  to view this content.

is there a function I can use to return the range where the text in a given column (ie. A) matches 'Management' ?

i.e the result for the example above would be A2:A6 ?

[Glenn Kennedy]

Cell with first occurrence:
=ADDRESS(MATCH("Management",$A$1:$A$13,0),1)

Cell with last occurrence:
=ADDRESS(LOOKUP(2,1/($A$1:$A$13="Management"),ROW($A$1:$A$13)),1)

[Andy Pope]

One way, where D2 contains Management, is this array formula so use CTRL+SHIFT+ENTER to commit formula

=ADDRESS(MIN(IF($A$1:$A$13=D2,ROW($A$1:$A$13))),1,4 ) & ":" & ADDRESS(MAX(IF($A$1:$A$13=D2,ROW($A$1:$A$13))),1,4)


Alternative normal formula
=ADDRESS(MATCH(D2,$A$1:$A$13,0),1,4)&":"&ADDRESS(MATCH(D2,$A$1:$A$13,0)+COUNTIF($A$1:$A$13,D2)-1,1,4)

[Glenn Kennedy]

As a matter of interest: whay do you want this? Are you wanting to use these in another formula, or are the addresses themselves the final result?

[SteveMG]

Cheers Glenn,

I should have said above but didn't for simplicity, which was probably a mistake - the lookup is being done from another sheet, This is what I've got in the cell I need to do the lookup in...

=VLOOKUP(C80,'Discplines & Levels'!ADDRESS(MATCH(B80,$A$1:$A$100,0),1):ADDRESS(LOOKUP(2,1/($A$1:$A$100=B80),ROW($A$1:$A$100)),1),4,FALSE)

Cell C80 contains the value I need to find, B80 contains the Discipline (i.e. Management). The list of disciplines as partly shown in the Orginal post is in Column A, covered by the A1 to A100 range.

This is what it was before
=VLOOKUP(C80,'Discplines & Levels'!$B$25:$E$28,4,FALSE)

Basically its the 'Discplines & Levels'!$B$25:$E$28 bit that I'm trying to make dynamic - at the moment I'm having to code the ranges by hand.

I'm sure the ADDRESS functions you used would work, but I need to somehow join the worksheet name onto the front of the range?

To give the context, I'm building up a series of SQL insert statements from the data and need to find the ID for the specific Level ID from column E in the 'Discplines & Levels' worksheet for the Discipline - i.e. 'Management'.

So management contains 4 levels, say IDs 4,5,6,7, I'm using the VLookup to find the ID for Management Level 4, but I need to find the range for "Management" so that the VLookup can the work on that range.

[Glenn Kennedy]

Not sure I completely follow you, but...

[Andy Pope]

using Glenn's file as the example. Rather than ADDRESS you can use INDEX to create top left and bottom right range references.

=VLOOKUP(E2,INDEX(Sheet1!$B$1:$B$13,MATCH(Sheet2!D2,Sheet1!$A$1:$A$13,0)):INDEX(Sheet1!$D$1:$D$13,MATCH(D2,Sheet1!$A$1:$A$13,0)+COUNTIF(Sheet1!$A$1:$A$13,D2)-1),3,FALSE)

[SteveMG]

Cheers Andy and Glenn, that worked perfectly. Substituting the cells for the cells in my spreadsheet was half an hours work, 15 min of which was fighting Excel as it wanted to put references to the different excel file every time I alt-tabbed to it! lol - Quickly solved that, thankfully I've got a big screen and could put about 4 windows into the 2 docs side by side!

But end result is that it works perfectly, that's a major pain the backside fixed