Multiple VLOOKUP LOOKUP IF Statement?

问题:

I've got the below tables.
What I need to do is have a VLOOKUP/LOOKUP statement which looks a the month under 'Compare' and tells me if that month is included under the column 'Month,' If it is, then look at Sent and Paid. If both columns have Yes in then then return Yes, otherwise return the value in the Paid column.

It does however need to check if say 1st Feb is inbetween the months, so in this example, if 1st Feb is between 1st Jan and March, use the data from Jan. At the moment I am 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 up to Sent, and 6 is for the Sent column. At the moment I am not included the Paid column which I need to do as explained above.

  Month          Sent?          Paid?
  1st Jan        Yes            Yes
  1st Mar        Yes            No
  1st Jun        No             No
  1st Oct        N/A            N/A

 Compare
 1st Jan
 1st Feb
 1st Mar
 ...
 1st Jun

回答1:

This answer relys on these assumptions:

1. Month and Compare values are enterd as DateSerial numbers (not strings)
2. Month data is sorted ascending (as in your sample data)
3. You havn't said which column Paid? is in. I have assumed R

Place this formula in an intermediate cell (lets assume A2 for this example)

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

This will return the index of the Month that is the larget value less than or equal to the Compare value. So for 1 Feb the index is for 1 Jan

Place this formula to get the required result

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