Does INDEX and MATCH have to use arrays sorted in a particular order?

[jrtaylor]

I've attached a workbook which uses INDEX and MATCH on a list of trades that are sorted by date, newest date first. Each trade has two rows: The row which has the opening of the trade is always lower than the row which closes the trade.

To match the openings to closings I'm using an INDEX/MATCH formula. Once I have matched the openings to closings I can do further calculations.

The formula I'm using works for the first two rows of the trade history, but it fails on the third row and I can't figure out why. (The trade list must be sorted from newest to oldest, so the solution must take this into account)

Any thoughts to solve this?

Thanks!

[Fluff13]

How about, in O36 filled down
=IF(OR(N36="close",N36="s/l",N36="t/p"),IF(INDEX(N37:$N$171,MATCH(D36,$D37:$D$171,0))="buy",F36-(INDEX(F37:$F$171,MATCH(D36,$D37:$D$171,0))),(INDEX(F37:$F$171,MATCH(D36,$D37:$D$171,0)))-F36),0)*$O$32

[Limor_OP]

Hi,
I think you have a circular reference.

In O38 your lookup value is D38, where it is part of the range it makes the search on...
Maybe you need to adjust your range so it wont search the value on itself.....

=IF(OR(N38="close",N38="s/l",N38="t/p"),IF(INDEX(N$38:$N$171,MATCH(D38,$D$38:$D$171,0))="buy",F38-(INDEX(F$38:$F$171,MATCH(D38,$D$38:$D$171,0))),(INDEX(F$38:$F$171,MATCH(D38,$D$38:$D$171,0)))-F38),0)*$O$32

[jrtaylor]

Thanks. I tried both. Fluff, your formula works. Thanks!

Belinda when I use your formula the calc returns 0. Not sure why. But Fluff's solution doesn't cause a circular error message so I'll give it a go.

I appreciate your help!

[protonLeah]

Did you replace the original workbook? Those formulas don't match the current attachment (cf. cols D & N)
"...Each trade has two rows..." Is column D the trade ID?

[Limor_OP]

Hi,
I didnt provide you a solurion , just xopied your formula to mirror the error...

[Fluff13]

Fluff, your formula works. Thanks!

You're welcome & thanks for the feedback