2 Columns - Find Data to Last Item Entry & Date to Left of Cell

Hi,

I'm trying to use a VLookup type of function but find the data to let of column or cell. Why isn't there a Left Vlookup functionality?


I'm attempting to use this function:

=INDEX(E166:E347,MATCH(LOOKUP(9.99E+307,F166:F347,F166:F347,0))

E166:E347 = is a date range with dates going downward from current date to end of the year
F166:F347 = Is a calculated column that provides total or sum extracted from log sheet
When used by itself: LOOKUP(9.99E+307,F166:F347 - Finds last entry from right column data

I'm seeking a function that will find the last given total & then give me the date (to the left of it)

Thanks

No need for a Left Vlookup. You're already attempting to use the best approach.
Try this variation:

Please Login or Register  to view this content.

Does that help?

Maybe:

Please Login or Register  to view this content.

Please remember to use code tags for formulas/code

Hi Ron & protonLeah,

Thanks the quick response.

Your example works well except in some instances, my results are 0 (zero). In other instances my results are 0 because it's a future date. Therefore, for example & because I'm going to the end of they year, in the right column there is a a result is 0 for 12/28/14 (in left) column because nothing has happened yet.


For example, as of 08/15/14, I have a total of 10. Therefore, I'm expecting the date to show as '08/15/14'.

How can the '0' issue be resolved?

Hi Everyone,

I uploaded an example file

Thanks

Hi,

Can a if type of function be added in such if not greater than today's date & not 0 find last entry date?

I see...The original formulas assumed you wanted the last numeric entry in Col_D. That's not the case. You have zeros as default values.
This regular formula returns the date that corresponds to the last non-zero value

Please Login or Register  to view this content.

EDITED: I just noticed the "not greater than today's date" criteria:
Try this regular formula:

Please Login or Register  to view this content.

Is that something you can work with?

Use a helper column to replace the zero values with null.

Hi Everyone,

Great feedback - All suggestions works well.

Thanks