Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Thanked 0 Times in 0 Posts

    Searching Function (Excel 2000)

    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...
    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!!

  2. #2
    Silver Lounger
    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
    <pre>{=MAX(IF(ISERROR(FIND($B$6,B1:B4)),MIN($A$1:$ A$4),A1:A4))}</pre>

    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts