1

Excel Problem - please open this attachment file

I want to get the latest END BALANCE of customer, when the STATUS is success. Picture of the table and result wanted is attached above.

fixer1234
  • 27,064
  • 61
  • 75
  • 116
harimin
  • 25
  • 6

1 Answers1

1

Assuming that your data is sorted descending by column A, use in cell H4:

=IFERROR(INDEX($E$2:$E$17,MATCH(1,(($C$2:$C$17="success")*($B$2:$B$17=$G4)),0)),"error")

This is an array formula and must be confirmed with Ctrl - Shift - Enter.

Copy down.

teylyn
  • 22,498
  • 2
  • 40
  • 54
  • I have tried it but the result is "error" for all customer. – harimin Jan 30 '16 at 23:25
  • Sorry, it works very well. Thanks for your help! :D – harimin Jan 31 '16 at 00:17
  • I tried to add one more condition, that is the END BALANCE (E2:E17) should be >10,000, but failed. =IFERROR(INDEX($E$2:$E$17,MATCH(1,(($C$2:$C$17="success")*($B$2:$B$17=$G4)*($E$2:$E$17>10000)),0)),"error"). Please help, thanks. – harimin Jan 31 '16 at 01:43
  • That formula works fine for me, too. Remember to use Ctrl-Shift-Enter to confirm it, not just Enter. Also, please next time don't post a photo of your screen. I've had to type your data twice now in order to check my formula. Please copy the cells and paste into your question. Also, your original question has been answered, so please mark the answer as described in the [Tour](http://superuser.com/tour) – teylyn Jan 31 '16 at 06:57
  • Looking closer at your formula, there seems to be an invisible character (ascii code 63) after the $E$2 that you added. Try this: `=IFERROR(INDEX($E$2:$E$17,MATCH(1,(($C$2:$C$17="success")*($B$2:$B$17=$G4)*($E$2:$E$17>10000)),0)),"error")` – teylyn Jan 31 '16 at 07:06
  • Thanks, it works fantastic! I will copy paste my data next time. – harimin Jan 31 '16 at 17:47