Multiple expression VLOOKUP LOOKUP IF?

Question

I have the following tables.
I need to make a VLOOKUP / LOOKUP statement that looks like a month under “Compare” and tells me if this month is included under the “Month” column. If so, look at Sent and Paid. If there is Yes in both columns, then return Yes, otherwise return the value in the Paid column.

However, you need to check if the 1st of February is speaking. the interval between months, so in this example, if February 1 is from January 1 to March, use the data from January. I am currently doing this using a combination of VLOOKUP and LOOKUP:

=VLOOKUP(LOOKUP(I21,$L$4:$L$15),$L$4:$Q$15,6,FALSE) 

I21 is the Compare month, L4-L15 is the Month column, L4-Q15 is the full range to the sent, and 6 is for the Sent column. At the moment, I do not include the “Paid” column, which I need to do as described above.

  Month Sent? Paid? 1st Jan Yes Yes 1st Mar Yes No 1st Jun No No 1st Oct N/AN/A Compare 1st Jan 1st Feb 1st Mar ... 1st Jun 

Answer 1

This answer relies on these assumptions:

• Values
• Month and Compare are entered as DateSerial numbers (not strings)
• Month data is sorted in ascending order (as in your sample data)
• You did not indicate in which column Paid? . I assumed R

Put this formula in an intermediate cell (let's say A2 for this example)

 =MATCH(I21,$L$4:$L$7,1) 

This will return the Month index, which is a larger value less than or equal to the Compare value. So, for 1 Feb index for 1 Jan

Put this formula to get the desired result.

 =IF(AND(INDEX($Q$4:$Q$7,A2)="Yes",INDEX($R$4:$R$7,A2)="Yes"),"Yes",INDEX($R$4:$R$7,A2))