Finding Cell Address Range Value Based On Multiple Criteria

[AmplexusFatum]

Hello there,

I am trying to find the first and last cell address based on values in other columns. In the table example below, the values in all three cells will be changing from workbook to workbook, hence the need for a conditional selection.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]test[/TD]
[TD]asc[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]test[/TD]
[TD]asc[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]test[/TD]
[TD]asc[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]test[/TD]
[TD]asc[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]toy[/TD]
[TD]des[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]toy[/TD]
[TD]des[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]toy[/TD]
[TD]des[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]toy[/TD]
[TD]des[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Firstly, I need to test for an AND condition, that is A = "test" & B = "asc". Next, I need to identify the first and last cell in Column C that fits the mentioned criteria. Therefore in this case, it would be C1 & C4. Note that I need the cell address in string, and not the cell value. I am trying to create a manual formula range based on the mentioned criteria to be entered into my greater formula later on, such as C1:C4.

Similarly, if I were to test a different criteria, such as A = "toy" & B = "des", then my output would be C5 & C8 for the first and last cells that fit that criteria, respectively.

Note that the criteria are all already sorted, such that when you find the first A = "test" & B = "asc", which is C1, the data in Columns A and B continues on in such a fashion until it switches to "toy" and "des", and vice versa.

I have looked high and low, but could not find an answer given that I am a novice in Excel. I was thinking of using either the Cell function or Index/Match function to produce the cell address I need.

Ultimately, I would want to write a formula that could produce that range based on the criteria I've input, such as CELL("address",INDEX(_____) : CELL("address",INDEX(_____) etc., where the two formulas will output C1 : C4, for example.

I hope I have illustrated my problem clearly enough.

Thanks for all the help in advance!

Best,

AmplexusFatum

 

[Special-K99]

Isn't this just

="C"&MIN(IF((A1:A8="test")*(B1:B8="asc"),ROW(C1:C8)))&":C"&MAX(IF((A1:A8="test")*(B1:B8="asc"),ROW(C1:C8)))
Array formula, use Ctrl-Shift-Enter

You can replace the MAX() with
"C"&LOOKUP(2,1/(A1:A8="test")/(B1:B8="asc"),ROW(C1:C8))

 

[AmplexusFatum]

Isn't this just

="C"&MIN(IF((A1:A8="test")*(B1:B8="asc"),ROW(C1:C8)))&":C"&MAX(IF((A1:A8="test")*(B1:B8="asc"),ROW(C1:C8)))
Array formula, use Ctrl-Shift-Enter

You can replace the MAX() with
"C"&LOOKUP(2,1/(A1:A8="test")/(B1:B8="asc"),ROW(C1:C8))

Hey thanks for this. The first array formula works wonderfully if I were to input it into a cell. It outputs the values as expected in string.

However, when I tried to input it into my greater formula using the INDEX MATCH function, the values are off. Specifically, I am using:

Let your array formula = xxx

=IFERROR(INDEX(xxx, MATCH(TRUE,ISNUMBER(xxx),0)),0)

Would the xxx output C1:C4 as string in my INDEX MATCH formula?

Thanks!

 

[Special-K99]

The result of the formula C1:C4 is a string so you'd need to use

=IFERROR(INDEX(INDIRECT(xxx)...MATCH(TRUE(ISNUMBER(INDIRECT(xxx),0)),0))