if, index, match

I am setting up a payroll journal entry in Microsoft Excel 2000 (PC)

(Col A) (Col B) (Col C)
(Col D)
Employee Gross Employee Gross Amount
Paralegal Smith 1000.00 Accountant 1 Macro
Lawyer Joe 1500.00 President Jose Macro
Secretary Mary 900.00
Accountant 1 1000.00

Macro is:

=INDEX($A$7:$B$138,MATCH(C7,$A$7:$A$138,0),2)

The problem is when column c is a terminated or new employee
(President) excel returns #n/a, can excel return a zero "0" instead and
highlight the cell in a color.

Also I have attached a sample spreadsheet.

Thanks for all your help!

Attachment filename: book2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=609872
---
Message posted from http://www.ExcelForum.com/

=IF(ISNA(MATCH(C7,$A$7:$A$138,0)),0,INDEX($A$7:$B$ 138,MATCH(C7,$A$7:$A$138,0
),2))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"rsikin " wrote in message
...
I am setting up a payroll journal entry in Microsoft Excel 2000 (PC)

(Col A) (Col B) (Col C)
(Col D)
Employee Gross Employee Gross Amount
Paralegal Smith 1000.00 Accountant 1 Macro
Lawyer Joe 1500.00 President Jose Macro
Secretary Mary 900.00
Accountant 1 1000.00

Macro is:

=INDEX($A$7:$B$138,MATCH(C7,$A$7:$A$138,0),2)

The problem is when column c is a terminated or new employee
(President) excel returns #n/a, can excel return a zero "0" instead and
highlight the cell in a color.

Also I have attached a sample spreadsheet.

Thanks for all your help!

Attachment filename: book2.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=609872
---
Message posted from http://www.ExcelForum.com/

The macro works great!

Thank You!


---
Message posted from http://www.ExcelForum.com/

On the payroll data in col a and col b

Is an employee get 2 paychecks is there anyway for excel to find two
payments and add them up automatically?

There is a sample excel attachment.


---
Message posted from http://www.ExcelForum.com/