Results 1 to 2 of 2
Thread: Searching Function (Excel 2000)
2004-01-16, 04:27 #1
- Join Date
- Jul 2001
- Thanked 0 Times in 0 Posts
Searching Function (Excel 2000)
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...
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!!
2004-01-16, 05:10 #2
- Join Date
- Mar 2001
- Springfield, Ohio, USA
- Thanked 2 Times in 2 Posts
Re: Searching Function (Excel 2000)
If the search string is in B6, then the formula that you want is
This is an array formula, which means that you do not enter the braces. Excel adds them when you enter the formula by pressing <Ctrl>+<Shift>+<Enter>.<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>