If Then

[Guest]

I'm trying to find a formula that will continue to search for a row that
meets the specified IF criteria without returning the rows that do not. i.e.
I want the below reference range to provide the following based off of an IF
statement. (the actual solution needs to work for hundreds of rows)
1 Cat
2 Dog
3 Cat
4 Dog
5 Cat

Results:
1
3
5

 

[Rick Rothstein \(MVP - VB\)]

Try this... assuming your list is in Column A, put this formula in B1...

=MATCH(C1,A1:A200,0)

and put the following formula in B2 and copy it down...

=IF(OR(B1>=200,ISNA(MATCH($C$1,INDIRECT("A"&(B1+1)&":A200"),0))),"",B1+MATCH($C$1,INDIRECT("A"&(B1+1)&":A200"),0))

Notice that I have assumed a maximum row number of 200.... change this to
any number equal to, or greater than, the maximum number of rows you intend
to search. The 200 appears once in the first formula and three times in the
second one.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I forgot to mention... put the item text you are searching for in C1.

Rick

 

[Guest]

I still couldn't get this formula to work.

How exactly should this look?
E1 = Cat. I want the results to provide the next number from column A that
fits the 'Cat' criteria without a space.

A B C

1 50 Cat =MATCH($E$1,C3:C27,0)
2 32 Cat =IF(OR(B1>=200,ISNA(MATCH($C$1,INDIRECT("A"&B1+1)&

:A200"),0))),"",B1+MATCH($C$1,INDIRECT("A"&(B1+1)&":A200"),0))
3 12 Cat
4 14 Dog
5 62 Cat
6 33 Dog

Desired Result:
1 50
2 32
3 12
4 62

Thanks.

 

[Rick Rothstein \(MVP - VB\)]

Well, you seem to have changed the original question completely. Just so I
don't head off in the wrong direction again, can you clarify a few things?
You say "I want the results to provide the next number from column A", but
according to your posted list, it looks like Column A contains the numbers
1,2,3,4,etc. and Column B contains the numbers you really want to re-list
depending on the search word and Column C contains the word list you want to
search. Plus, your use of the range C3:C27 would seem to confirm that you
want to search a word list in Column C. However, your sample result list
would then appear to be the numbers from Column B... BUT, you said that "I
want the results to provide the next number from column A"!!! So, what is
really in what columns? Please be specific so we don't have to guess. ALSO,
your initial post indicated there would be hundreds of rows, yet the range
you specified in your example for the first formula was C3:C27. Two
questions... Why the maximum of row 27? More importantly, why are you
starting your search from row 3??? Please clarify that for us also.

Rick

 

[Guest]

"animal" is a defined name range (eg. B2:B100)
"result" is a defined name range (eg. A2:A100)

In C2:
=IF(ISERR(SMALL(IF(animal="Cat",ROW(INDIRECT("1:"&ROWS(result)))),ROWS($1:1))),"",INDEX(result,SMALL(IF(animal="Cat",ROW(INDIRECT("1:"&ROWS(result)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed


or you can use this formula

In C2:
=IF(COUNTIF(animal,"Cat")>=ROWS($1:1),INDEX(result,SMALL(IF(animal="Cat",ROW(animal)-MIN(ROW(animal))+1),ROWS($1:1))),"")

ctrl+shift+enter, not just enter
copy down as far as needed