google sheets - Lookup function in gsheets

Question

I'm trying to do a lookup with 2 criteria 'Date' and 'Name'. The problem is I just need payout info for the last two months for an individual and I want this VLookup formula to include the last two months by default so that I don't have to manually keep changing dates in formula each month.

question from:https://stackoverflow.com/questions/65924690/lookup-function-in-gsheets

Answer 1

I would go for FILTER and VLOOKUP combined together.

First you need to filter the range to remove all the values older than 2 months and then do the vlookup.

=vlookup(F3,filter(C2:D,B2:B>edate(today(),-G1)),2,false)

edate(today(),-g1) gives you the date 2 months before today filter(C2:D,B2:B>edate(today(),-G1)) makes a range without entries older than 2 months.

Vlookup looks for entries "billy" in temporary filtered range and returns it's 2nd column.

Remember: If there are more entries within 2 months period, vlookup will show only first one from the list. If you want all entries you should use filter function with 2 conditions.