Index match formula with mulitple criteria ('if' result less than/greater than 0)???

Hi There,


I have an index match formula(s) which always relates to two cells(effectively two match results). The normal index/match formula just returns the first match result.

One of the cells contains a negative number, the other a positive number. Does anybody know how to adjust the formula so it only returns the positive numbers,('y' below) additionally a formula to only return the negative values. ('x' below)

Data is a bit like so:

||||||| Col A ||||||| ||||| Col B |||||| Col C |||||| Col D |||||| Col E
Row 1 NEG No.s ||||| POS No.s ||||| PO's ||||| | PO's ||||| Values
Row 2 x1 |||| |||| ||||| y |||| |||||||| 101 ||||| || 101 |||| ||||| $100
Row 3 x2 |||| ||||| |||| y |||| ||||| || 131 ||||| || 101 |||| ||| -$500
Row 4 |||||||||||||||||||||||||||||||||||||||| ||| | 131 ||||| || -$200
Row 5 ||||||||||||||||||||||||||| |||||| ||||||||| | 131 ||||| |||| $400

currently 'x1' = index(E:E,match(C2,D:D,0)) and 'x2' = index(E:E,match(C3,D:D,0)) but this does not quite get the result I am after. Anybody able to help?

Cheers

Ryan

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic