# Lokup Function help needed

1. ## Lokup Function help needed

Sample.xlsxHi all,

Need some help on a excel issue with Lookup (possibly not using right function right now but that is what we started with).

Workshet A, we have data in columns A - D and then a date in column E that is the due date, and then the columns that we want to determine are column F which we want the release date and column G the release number.

Worksheet B, we have column A with the release date and then column B with the release number.

We want the due date to be less than or equal to the release date. Examples:
due date of 04/01/13 we would want the first row in worksheet B with release date of 04/15/13 (col. F) and release number #1 (Col. G)
due date of 04/30/13 we would want the second row in worksheet B with release date of 05/15/13 (col. F) and release number #2 (Col. G)
due date of 05/16/13 we would want the third row in worksheet B with release date of 06/15/13 (col. F) and release number #3 (Col. G)

Right now we have this formula
=IFERROR(LOOKUP(DATEVALUE(A4),DATEVALUE(Releases!\$A\$2:\$A\$100),Releases!\$B\$2:\$B\$100),"Before First Listed Date")

but on a due date of 05/01/13 it is returning release date of 04/15/13 and release # of 1.

Any help would be greatly appreciated!! Thank you!!

2. ## Re: Lokup Function help needed

Hi webba05,

welcome to the forum.
Please upload a sample workbook along with your expected output. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

3. ## Re: Lokup Function help needed

Hi
can you tell me what kind of data is in columns A-D?

4. ## Re: Lokup Function help needed

Thank you dilipandey, I updated the first post with the sample. I took the data out of the columns before it because it is just text that does not really have anything to do with the dates. Thanks for looking at this!!

5. ## Re: Lokup Function help needed

Hello,

I have to used array for it to work, and my formula is
=INDEX(Releases!\$A\$2:\$A\$99,SMALL(IF(Releases!\$A\$2:\$A\$99>=\$A2,ROW(Releases!\$A\$2:\$A\$99)-ROW(\$A\$2)+1),1))
When you enter the formula, you have to use ctrl-shift-enter for it to work, because it's an array formula.
Similarly, for the Release column,
=INDEX(Releases!\$B\$2:\$B\$99,SMALL(IF(Releases!\$A\$2:\$A\$99>=\$A2,ROW(Releases!\$A\$2:\$A\$99)-ROW(\$A\$2)+1),1))
But the funny part is, for the row with Due Date of 03/01/2013, it returns the value of 04/15/2013.
Please let me know if this is the kind of results you are looking for, meanwhile I'll try to work around lookup with this.

Regards

6. ## Re: Lokup Function help needed

both sets of "dates" you are using are text, not dates (as you already know), so try this....

1st, add a helper column to your "dates" on sheet2 (I used C) and convert your "text" to dates with...
=A2*1

then on sheet1, use this, copied down...
=INDEX(Releases!\$B\$2:\$B\$8,MATCH(A14*1,Releases!\$C\$2:\$C\$8,1)+1,1)

7. ## Re: Lokup Function help needed

That worked!! Thank you so much!! That is really going to help us!!

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1