# Last entry in a range before date defined by a cell

1. ## Last entry in a range before date defined by a cell

so, i have two sheets.
one of them with a bunch of entries by date.
on the second one i want a formula to pick the last entry before date on another cell.

what i've got so far is: =index(sheet1!A:A;if(and(sheet1A:A<=G2;sheet1!A:A<>"");max(sheet1!A:A<>"")*(LIN(sheet1!A:A))))

the problem with this formula is that its showing all entries not empty in a list (instead only the last result) and also showing results above the date set on G2

i think there might be a even better formula for what I want... although this method should work...

cells in sheet1!A:A are dates, as well as data in G2.

2. ## Re: Last entry in a range before date defined by a cell

I have no idea what LIN function is, but below helps to get last date occurance in A:A that earlier date in G2

=lookup(2;1/(sheet1A:A<=G2)/(sheet1!A:A<>"");sheet1!A:A)

3. ## Re: Last entry in a range before date defined by a cell

It's always best to attach a sample file (see the yellow banner at the top). That way we don't have to guess as much and we don't have to re-type any data. Anyway, because you're on MS365, you might be able to use the Xlookup formula, although I'm not entirely sure I understand what you mean when you say, " pick the last entry BEFORE date on another cell". (This is why a sample sheet with MOCKED UP answers is helpful).

=XLOOKUP(G2,sheet1!A:A,return array,,-1,-1)

4. ## Re: Last entry in a range before date defined by a cell

Thanks for the tips both of you!
i got what i need.
but lookup function worked there much better after a few adjustments for spreadsheet.
Next time i will try to be more clear and upload something to help you help me

rly appreciate the help

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