Average of Vlookup data

  • Thread starter Thread starter JAbels001
  • Start date Start date
J

JAbels001

I have 263 employees and I am trying to average out third quarter, so I have
tabs July, Aug, and Sept and a fourth tab for the average. I have built an
average formula using vlookup (by their employee id) to simplyfy it, BUT I am
getting an error (#N/A) for about 50 employees b/c they were only here for
one or two months... The problem is that I do not know which month without
looking through all of the data for each month.

Is there a formula that could look up this info for me and average it while
neglecting the missing data?

Current formula:
=AVERAGE(VLOOKUP(A6,JulGen,4,FALSE),VLOOKUP(A6,AugGen,4,FALSE),
VLOOKUP(A6,SeptGen,4,FALSE))
 
Change each of the VLOOKUP's to this (using the appropriate table name for
each):

IF(ISNA(VLOOKUP(A6,JulGen,4,0)),{""},VLOOKUP(A6,JulGen,4,0))
 
That worked perfectly! You're a genius! Thanks!

T. Valko said:
Change each of the VLOOKUP's to this (using the appropriate table name for
each):

IF(ISNA(VLOOKUP(A6,JulGen,4,0)),{""},VLOOKUP(A6,JulGen,4,0))
Click to expand...
 
Back
Top