• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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.
0
Mehawitchi
Asked:
Mehawitchi
3 Solutions
 
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
 
Rgonzo1971Commented:
Hi,

Why not use

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

Open in new window

Regards
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
Here is a sample
lookup-Average.xlsx
0
 
barry houdiniCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now