CODEWITHSUNDEEP
excel
Shingi Muvenge
Shingi Muvenge

Reputation: 11

Index Match where Match numbers are not exact

I am having a bit of an issue with the Index Match functions. I am trying to index match where the match is for values that are closest to each other, in essence there might be rounding differences in my data which make exact matches impossible. How would I go about this for eg

5,778,590.25 is my reference number

I would like this matched to 5,778,591.00 in the below row to pull up the trade date;

Trade Date CCY Amount
01/08/2016 USD 5,778,591.00

Assuming Trade Date lies in column A1

Upvotes: 0

Views: 1912

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

One way to do approximate matches is with the third argument of the MATCH function. However, this doesn't necessarily behave well if your values aren't sorted. So I'd suggest something like this:

=INDEX(B:B,MATCH(MIN(ABS(B:B-Ref)),ABS(B:B-Ref),0))

where your amounts are in column B and Ref is your reference number. This finds the closest value to your reference number whether it's greater or less than it.

Note that this is an array formula, so it will need to be entered using Ctrl+Shift+Enter to work properly.

You may want to wrap it inside an IF function that will tell you "No Match" if it's not close enough to any of the numbers. E.g.

=IF(MIN(ABS(B:B-Ref))>5,"No Match",INDEX(B:B,MATCH(MIN(ABS(B:B-Ref)),ABS(B:B-Ref),0)))

which will return "No Match" if there aren't any values within $5.

Upvotes: 1

Related Questions