Help with combining these two formulas VLOOKUP and AVERAGE

Hi I'm currently using a vlookup formula to find the value in a single sheet:

=vlookup($A4,'Week 1'!$B:$E,3,0)

i need to find the average value of the same formula but for four different sheets (Week 2, Week 3, Week4)

rather than using four cells and using vlookup to find the value individually and then finding the average using =Average, is there a way of combining the two formulas into one cell.

Thanks

Maybe

=AVERAGE(VLOOKUP(...),VLOOKUP(...),VLOOKUP(...),VLOOKUP(...))

Well it's the same thing that you describe, it's just doing it all in one cell:

=AVERAGE(IFERROR(vlookup($A4,'Week 1'!$B:$E,3,0),0),IFERROR(vlookup($A4,'Week 2'!$B:$E,3,0),0),IFERROR(vlookup($A4,'Week 3'!$B:$E,3,0),0),IFERROR(vlookup($A4,'Week 4'!$B:$E,3,0),0))

Hope this helps.

Pete

Well thats me put to shame.

I'm sure i tried the exact same formula before i came on here as thats the obvious solution but i was getting an Error code.

Now however after re-entering it, it works fine.

Thanks for your help

If you didn't wrap the IFERROR around the VLOOKUPs then you would have got an error result if any of them didn't find an exact match - mine just returns 0 in that case, and AVERAGE will ignore them.

Hope this helps.

Pete

Originally Posted by kraser

I'm sure i tried the exact same formula before i came on here as thats the obvious solution but i was getting an Error code.

Now however after re-entering it, it works fine.

Been there, done that, as I'm sure many other have as well

To correct the average, try changing the iferror results from 0 to ""

unfortunately i tried this and it come up with the error:

function AVERAGE parameter 3 expects number values. But " is a text and cannot be coerced to a number

This works for me, it just returns a #VALUE error if all 4 vlookups return N/A

=AVERAGE(IFERROR(VLOOKUP($A4,'Week 1'!$B:$E,3,0),""),IFERROR(VLOOKUP($A4,'Week 2'!$B:$E,3,0),""),IFERROR(VLOOKUP($A4,'Week 3'!$B:$E,3,0),""),IFERROR(VLOOKUP($A4,'Week 4'!$B:$E,3,0),""))

You could also do

=SUM(all_those_vlookups) / COUNT(all_those_vlookups)

where the IFERROR term returns "" instead of 0 when there is an error.

Hope this helps.

Pete