Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    San Jose, CA, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an OpenOffice spreadsheet which has names in col A and numbers in column C of rows 24-40. In row 43, I show the lowest number found in rows 24-40, and in row 44, I show how many times that lowest number appeared.

    In row 45, if the lowest number (row 43) only appeared once (col 44) in rows 24-40, I want to show the corresponding value from col A.

    Here is my formula:

    =IF(C44=1;LOOKUP(C43;C24:C40;$A24:$A40);"")

    I get a blank if the lowest number is not unique (C44 <> 1), but the problem is if the number is unique, this works sometimes, and sometimes I get #N/A (Error: Value not available). I can't figure out why I sometimes get the error.

    Any ideas would be greatly appreciated.

    Thanks ... John

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    San Jose, CA, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I guess the answer is that the data in the search range needs to be in ascending order to get useful results. In my case, it is not. Don't think I can use VLOOKUP either. Hmmmm.

    Thanks ... John

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    If sorting the data is not an option, Col A could be copied/formula to Col D ( or some other column to right of Col C) and then use VLOOKUP.

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    San Jose, CA, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, that's exactly what I did!

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John S Lawrence View Post
    I guess the answer is that the data in the search range needs to be in ascending order to get useful results. In my case, it is not. Don't think I can use VLOOKUP either. Hmmmm.

    Thanks ... John
    Try...

    =IF(C44=1;INDEX($A24:$A40;MATCH(C43;C24:C40;0));"" )
    Microsoft MVP - Excel

Posting Permissions

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