CODEWITHSUNDEEP
excelexcel-formula
zlinks
zlinks

Reputation: 1067

excel get row range or actual values based on matching value

I have the following structure in my excel sheet.

          A         B
      ---------   -----  
 1 |  category1   test1
 2 |  category1   test2
 3 |  category1   test3
 4 |  category2   test4
 5 |  category2   test5
 6 |  category3   test6
 7 |  category3   test7
 8 |  category3   test8
 9 |  category3   test9
10 |  category4   test10

Can you suggest a way using EXCEL functions (not VBA) of getting back either the range or the actual values given the category text as input?

For example given category2 I need to get back something like B4:B5 or "test4"/"test5"

I need a way of doing this dynamically i.e. I need a function that will still work if I do an insert and add a new value to a category e.g. 

          A         B
      ---------   -----  
 4 |  category2   test4
 5 |  category2   test5
 6 |  category2   test200
 7 |  category2   test203

Now given category2 I would get back B4:B7

Upvotes: 2

Views: 327

Answers (2)

SierraOscar
SierraOscar

Reputation: 17647

If your category always starts with the same prefix, you could use something like:

=ADDRESS(MATCH("category"&$C$1,B:B,0),2,4,1)&":"&ADDRESS(MATCH("category"&($C$1+1),B:B,0)-1,2,4,1)

So if you put "2" in C1, it will return "B4:B5"

This also assumes that categories are named in increasing values, so if there was no category3 this would not work.

Upvotes: 0

ttaaoossuu
ttaaoossuu

Reputation: 7894

Assuming categories are always contiguous, you can get reference:

=OFFSET($A$1,MATCH("category2",$A:$A,0)-1,1,COUNTIF($A:$A,"category2"))

Upvotes: 2

Related Questions