Results 1 to 5 of 5

Thread: Search (XL)

  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search (XL)

    Hi

    I am trying to create a variant on an IF AND VLOOKUP theme. The basic statement =if(and(a<=b, d>=c)),vlookup(a1,b1:b500,2,false),0) pulls back what I want but it also gives me a lot of lines with 0 on which I don't want. How can I get the statement to ignore any lines that return false and just display those that return true?

    The two comparisons in the IF AND are both dates.

    Hope this make sense!

    Many thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search (XL)

    How about replacing 0 with "" ? If the condition is false, the formula will return the empty string "", leaving the cell (looking) blank.

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search (XL)

    Hi

    Thanks for this. I need to sort on Date descending to avoid getting 'blank' rows in the result but it works fine.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search (XL)

    Isn't it possible to define a custom sort to avoid this? Haven't a clue how, but if it's poss, no doubt some nice wise one here will tell us!

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search (XL)

    The Excel default is to sort the cells displaying null strings last, which is I think what the original poster wanted. No custom sort order required. I think he was just saying that he had to do the sort.
    Legare Coleman

Posting Permissions

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