Add two VLOOKUP results together in VBA

[irv]

I'm not too familiar with VBA. I have a macro which returns the results for

=IF(ISNA(VLOOKUP("EMPLOYEE",Z:AA,2,0)),0,VLOOKUP("EMPLOYEE",Z:AA,2,0))

As of today, the entry EMPLOYEE is sometimes written as it was—EMPLOYEE, and sometimes as EMP. I have to add these two together. How do I change the code of the macro?

Before it was:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(""[EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"

I tried "=IF(ISNA(VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"+"IF(ISNA(VLOOKUP(""EMP"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMP"",C[-16]:C[-14],2,0))"

as I would in Excel, but of course it doesn't work. Any help would be appreciated.

[JOHN H. DAVIS]

Maybe:

=IF(ISNA(VLOOKUP("*" & "EMP" & "*",Z:AA,2,0)),0,VLOOKUP("*" & "EMP" & "*",Z:AA,2,0))

[irv]

Thanks for taking the time to answer. I think I didn't make myself clear. I have two columns, Z and AA. Z is alpha and AA is numeric. I have to aggregate column AA for all the appearances of 'EMP' and of 'EMPLOYEE' in column Z throughout the 200,000 rows of the report. My macro only looked for 'EMPLOYEE' until now, but as of today, the report uses EMP and EMPLOYEE interchangably, and I have to combine the totals for EMP and EMPLOYEE in my report. I have the same issue with five more terms which have been shortened (CORP and CORPORATE, EDU and EDUCATION, etc.), and I have to add together the long form and the short form when aggregating.

It's a long and complicated macro, so I was wondering if someone can tell me how to replace the VBA code of that VLOOKUP so that it will not only look for EMPLOYEE, but rather it will add all appearnces of both EMP and EMPLOYEE.

[Miraun]

Thanks for taking the time to answer. I think I didn't make myself clear. I have two columns, Z and AA. Z is alpha and AA is numeric. I have to aggregate column AA for all the appearances of 'EMP' and of 'EMPLOYEE' in column Z throughout the 200,000 rows of the report. My macro only looked for 'EMPLOYEE' until now, but as of today, the report uses EMP and EMPLOYEE interchangably, and I have to combine the totals for EMP and EMPLOYEE in my report. I have the same issue with five more terms which have been shortened (CORP and CORPORATE, EDU and EDUCATION, etc.), and I have to add together the long form and the short form when aggregating.

It's a long and complicated macro, so I was wondering if someone can tell me how to replace the VBA code of that VLOOKUP so that it will not only look for EMPLOYEE, but rather it will add all appearnces of both EMP and EMPLOYEE.

Sure... but do you need it to be a Vlookup Formula? Or can you just have it be the Vlookup RESULT in the macro?

Please Login or Register  to view this content.

That may need a little alteration, but should put you on your way. I've never used the C[-16]:C[-14] syntax before.