Searching Function (Excel 2000)

Topic

New Reply

Hello all,

Here is what I want to do, but can’t quite seem to figure out what will work. I have two columns, Column A is numbers, column B is text. What I want to do is find the largest value in Column A that has a certain specified string in column B in the same row. For instance…
—-A——–B
1| 20 whatever
2| 22 anything
3| 25 anything ever
4| 30 who cares

So suppose I want to return the maximum value in A (which contains ascending values) that contains the string “ever” in column B. Both row 1 and 3 in column B contain the string “ever”, I want my function to return the value in row 3 Column A in this case. I want it to return the value 25. How can I do this??? I would like to stay away from writing my own function (I tried but also got a bit stumped). If possible I would just like a formula, but if a function is the only way to go, I would appreciate some help on that.

Thanks a bunch!!

Reply | Quote

Replies

If the search string is in B6, then the formula that you want is

{=MAX(IF(ISERROR(FIND($B$6,B1:B4)),MIN($A$1:$A$4),A1:A4))}

This is an array formula, which means that you do not enter the braces. Excel adds them when you enter the formula by pressing ++.

Reply | Quote

If the search string is in B6, then the formula that you want is

{=MAX(IF(ISERROR(FIND($B$6,B1:B4)),MIN($A$1:$A$4),A1:A4))}

This is an array formula, which means that you do not enter the braces. Excel adds them when you enter the formula by pressing ++.

Reply | Quote