get cell address range based on match/lookup

[psynce]

column A has many rows containing 123, ABC, or XYZ. column A is already sorted. i use the following forumla to return the address of the fist cell matching ABC:


=ADDRESS(MATCH("ABC",A:A,0),2)


i would like to do something similar to either return the address of the last cell in column A that mataches ABC, or something that returns the range of cells that match.


thank you in advance for any thoughts that you may have ...

 

[SirJB7]

Hi, psynce!

If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type related keywords and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

Regards!

 

[kchiba]

Hi psynce,


You can use a helper column with a countif formula so in column B you will have COUNTIF(A1:$A$64000,"ABC"),note that A1 is not absolute, copy this formula down. You must then search in Column B for the first 0, the row would be the found row less 1. The ADDRESS formula should be something like =ADDRESS(MATCH(0,B:B,0)-1,1)


hope this helps

 

[NARAYANK991]

Hi ,


Try this :


=ADDRESS(MAX((Data_column=Match_value)*ROW(Data_column)),COLUMN(Data_column))


Data_column is the column address for your data e.g. A:A


Match_value is the value which you wish to match e.g. ABC


The above formula is an array formula , to be entered using CTRL SHIFT ENTER.


Narayan

 

[psynce]

Wow - Thank you for all for your responses!


SirJB7: I searched rather extensively but was unable to locate a solution.


kchiba: i like your helper column approach for other uses, however, i am trying to get start and end cell addresses for multiple values/matches in a single column. using my example, i would need a helper column for each value/match: "123", "ABC", "XYZ", right?


NARAYANK991: i have been working on the array formula that you provided, but cannot quite seem to get it ... the closest i have gotten returns "#N/A"


'=ADDRESS(MAX((A:A="ABC")*ROW(1:64000)),COLUMN(A:A))'

 

[SirJB7]

Hi, psynce!


Tried with NARAYANK991's formula like this?

=ADDRESS(MAX((A:A="ABC")*ROW(A:A)),COLUMN(A:A))


Regards!

 

[psynce]

Hazaa! NARAYANK991 & SirJB7 have done it!


Thank you all so much for helping me solve this riddle. Such is the beauty of life, that when an answer comes to light, so are new questions illuminated =)


|||||[ MANY THANKS TO ]|||||

NARAYANK991 & SirJB7 & kchiba