if, index, match

[rsikin]

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 employe
(President) excel returns #n/a, can excel return a zero "0" instead an
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=60987

 

[Peo Sjoblom]

=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]

The macro works great!

Thank You

 

[rsikin]

On the payroll data in col a and col b

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

There is a sample excel attachment