Vlookup - vlookup

[JOHANNA]

I have a workbook with several worksheets. I have one worksheet with a table of "pension payment" which is a flat dollar amount for half of my employees. Several other tabs are by pay date with salary and pension payment information. I have one tab that is a summary sheet.

I would like a formula on my summary sheet that would look up the value on the pension payment tab and subtract any payment received on the specific pay date tab.

Currently I tried the following formula for example:

=IF(ISERROR(VLOOKUP($A3,'Pension Payment'!$A$2:$E$189,5,0)-VLOOKUP($A3,'W-E 10-03-2010'!$B$3:$J$252,9,0)),0,(VLOOKUP($A3,'Pension Payment'!$A$2:$E$189,5,0)-VLOOKUP($A3,'W-E 10-03-2010'!$B$3:$J$252,9,0)))

The problem is that not everyone who gets a pension payment shows up on every pay date tab. When the employee shows up on the Pension Payment tab, but not on the pay date then I get a 0 result. Is there a way to specify that if they are on the Pension Payment tab, but not on the pay date tab that I want to subtract 0 from the Pension Payment information?

 

[Aladin Akyurek]

Wouldn't be more appropriate to invoke a SumIf formula here?

Something like:

=SUMIF('Pension Payment'!$A$2:$A$189,$A3,'Pension Payment'!$E$2:$E$189)-
    SUMIF('W-E 10-03-2010'!$B$3:$B$252,$A3,'W-E 10-03-2010'!$J$3:$J$252)

 

[Jonmo1]

Try

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP($A3,'Pension Payment'!$A$2:$E$189,5,0),VLOOKUP($A3,'Pension Payment'!$A$2:$E$189,5,0)-VLOOKUP($A3,'W-E 10-03-2010'!$B$3:$J$252,9,0)))

 

[JOHANNA]

Those work wonderfully! Thank you very much!

 

[Aladin Akyurek]

Those work wonderfully! Thank you very much!

You are welcome. Thanks for providing us feedback.