• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Date look up please

A

adown

New Member
Hi everyone, I found this formula on date look up from this website. This is awesome. I didn't know Excel would do so much. Here is the formula I am using =IF(SUMPRODUCT(--(E6:E15<=F3)*(F6:F15>=F3))=1,"Row no. "&SUMPRODUCT(--(E6:E15<=F3)*(F6:F15>=F3),ROW(E6:E15))-5,"Not Found") . It is looking in column E and F, and returning a row number. What I would like to do is return text that is in the corresponding row in column G. I understand a little of whats going on, but I don't know how to tell it what to give me. Thanks, adown
 
Adown


=IF(SUMPRODUCT(--(E6:E15<=F3)*(F6:F15>=F3))=1,"Row no. "&SUMPRODUCT(--(E6:E15<=F3)*(F6:F15>=F3),ROW(E6:E15)),"Not Found")


Does it for me


You will only get a valid result provided there is only 1 row that meets the criteria
 
Yes, it does work. Let me explain myself. I am using this for positions held in my company. I have "from date" in column E,"to date" in column F, and "position held" in column G. Instead of returning the row # where the date is found, I would like to return the position held, which would be in the same row, but in the next column over. It seems simple enough, but I'm new at Excel. Thanks for your help, adown
 
Adown

Try This beauty:

Code: 
=IF(SUMPRODUCT(($E$6:$E$15<=$F$3)*($F$6:$F$15>=$F$3))=1,INDEX($G$6:$G$15,SUMPRODUCT(($E$6:$E$15<=$F$3)*($F$6:$F$15>=$F$3),ROW($E$6:$E$15))-ROW($G$5)),"Not Found")
 
This retuns Not Found. I am using A2- A21 for from date, B2-B21 for to date, D2-21 for positions held, B25 for look up date, and B26 for results. Here is what I did to your formula to adapt it. I'm sure it works, I've done something wrong. Thanks again for your time, adown


=IF(SUMPRODUCT(($A$2:$A$21<=$B$25)*($B$2:$B$21>=$B$25))=1,INDEX($D$2:$D$21,SUMPRODUCT(($A$2:$A$21<=$B$25)*($B$2:$B$21>=$B$25),ROW($A$2:$A$21))-ROW($D$1)),"Not Found")
 
It Works ok in those ranges

Not Found means there is either no data or 2 or more rows which have valid answers


Have you checked manually ?
 
Yes, It works. After I wrote last, I saved and shut down. After getting ready for work, I decided maybe I missed something even though I checked everything 10 times. after a computer restart, it was working flawlessly. It wouldn't work until I shut down. Strange. Thank you very much for your formula and subsequent help, it works like a charm. adown
 
You must log in or register to reply here.
Back
Top