asked on

Using Match in an Excel Range

Hi Experts,

I have a table with a number of entries that have the date and amount of the transactions. Some of the amounts repeat throughout the table.

I would like to see if there is a way to look up the 1st occurrence of an amount that is in the table on or after the date of the transaction.

For example the table will have several transactions with an amount of 500. If the transaction I want to match is dated for 07/01, then I want to match it with the first time the amount of 500 occurs on or after 07/01.

I have attached a spreadsheet with the table in columns A:B and a sample transaction in columns K:M.

Any help on this would be great.

Thanks in advance,

JJ
ranged-match.xlsx

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JJINFM

ASKER

Wow! That worked great.

I don't understand how "INDEX(($A$1:$A$215>=K2)*($B$1:$B$215=L2),0)" returns the value need to match

NBVC

Each of these 2 conditions ($A$1:$A$215>=K2) and ($B$1:$B$215=L2) returns an array of TRUE/FALSEs based on the evaluation of the checks. When you multiply arrays of TRUE/FALSE together, they result in an array of 1's and 0's (where TRUE*TRUE = 1, and all other combo's result in 0). The INDEX() just allows the array to hold without having to confirm with CTRL+SHIFT+ENTER as you would normally need for an "array" formula.... then MATCH() looks for the first 1 in that array (which is the first time both conditions checked are TRUE.

JJINFM

ASKER

ok, thanks for the explanation. I'll keep that in mind