Index Match but only return value if cell to right and above 1 is a negative.

Hi Everyone,

I have an array formula which is working as expected returning the value of column K (offset by -1) if a number of criteria are matched.

Please Login or Register  to view this content.

The issue I'm having is there are occasionally duplicate values in column W and it's using the first value in its return, which isn't always correct.

Unfortunately due to the limited matches available the only way I can think to identify the correct value is by adding an additional criteria, which is that the correct value would always have a negative in Column X one cell above and to the right of where the value is being retrieved from. If there was no negative it would ignore this result.

Is there any way to add an additional criteria to the formula to stipulate that it must match a negative in column X but one cell above?

Thanks for any help offered.

Hi Dave,

I think this may be a task for SUMPRODUCT.

Are you able to post a sample workbook?

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,

Thanks for responding. I've attached a sample workbook showing the issue, which is basically that I need to add one more criteria (a negative above to the right) to ensure that the formula returns the correct period, not just the first it comes across.

Thanks

Dave

Hi Dave,

I have to go to Ballarat tomorrow, but will look at it soon (if someone else doesn't pick it up).

Regards,

David

David, I appreciate the effort.

Cheers

Thanks icestationzbra, much appreciated for helping me with this one. The solution works well.

Cheers

David