CODEWITHSUNDEEP
excelexcel-formula
flippind
flippind

Reputation: 23

Find first match in two tables

I've got two tables (Amortization (A) and Compounding (C)) and I'm trying to find the first DateA where DateA > DateC AND ValueA < ValueC. I assume it'll be something like Index(DateA,Match(And()), but I'm not entirely certain what the matches would be. So, given the below tables, the returned value would be 2/1/2044 (DateA = 1/31/2044<2/1/2044 AND $38,185.18>$37,767.32). 

        Compounding                     Amortization
 Value          Date              Value          Date
 $37,894.51    11/22/2043         $41,751.75    11/1/2043
 $37,952.62    12/6/2043          $40,428.16    12/1/2043
 $38,010.74    12/20/2043         $39,100.03    1/1/2044
 $38,068.88    1/3/2044           $37,767.32    2/1/2044
 $38,127.02    1/17/2044          $36,430.04    3/1/2044
 $38,185.18    1/31/2044          $35,088.16    4/1/2044
 $38,243.35    2/14/2044          $33,741.67    5/1/2044
 $38,301.53    2/28/2044          
 $38,359.73    3/13/2044

Edit 6/30: Updated example data to more closely reflect actual scenario

Upvotes: 2

Views: 65

Answers (2)

pnuts
pnuts

Reputation: 59440

Maybe, assuming above are in ColumnA:D, please try selecting those columns and: Format - Conditional formatting..., Custom formula is and: 

=AND($C1-$A1>0,$C2-$A2<0)

with yellow fill. Done.

Adjust the Applies to range to A2:D8, assuming that is where 5/1/2044 resides.

Note (may be very relevant) above ignores the dates and relies purely on when the value column Amortisation drops below that for Compounding.

Upvotes: 1

A.S.H
A.S.H

Reputation: 29332

Array (CSE) formula:

=INDEX(D:D,MATCH(1,(D:D>B:B)*(C:C<A:A),0))

Ctrl Shift Enter

Upvotes: 0

Related Questions