[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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