How to use VLookUp or any other function to retrieve average values of cells?

Hello experts,

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

Is there anyway I can return all cells that meet my search criteria and average them, rather than only getting the first match.
MehawitchiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
barry houdiniConnect With a Mentor Commented:
If you are using excel 2007 or later then AVERAGIF function allows you to average data based on a criterion, e.g. to average all cells in column B when column A = "x"

=AVERAGEIF(A:A,"x","B:B)

regards, barry
0
 
Saqib Husain, SyedEngineerCommented:
You can use MATCH to get the row number.

From that you can use the OFFSET function to get a number of columns in that particular row and then you can average it.
0
 
MehawitchiAuthor Commented:
Thanks  ssaqibh

I'm not quite sure how to build this into a formula

Can you show me an example please
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Why not use

=SUMIF(D1:D5,1,E1:E5)/COUNTIF(D1:D5,1)

Open in new window

Regards
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Here is a sample
lookup-Average.xlsx
0
 
MehawitchiAuthor Commented:
Barry's solution is exactly what I need.
Thank you all
0
 
Saqib Husain, SyedEngineerCommented:
I clearly misunderstood your requirements. You should have awarded all points to Barry.
0
 
MehawitchiAuthor Commented:
No worries ssaqibh. You made a good attempt after all
0
All Courses

From novice to tech pro — start learning today.