# Find a value based on criteria

Trying to calculate a score based on criteria that the user inputs.  On sheet 1 user enter Male or Female, their age, and number of PU they did and in Column D I need to calculate the score based on the data from sheet 1.  An example of a Male, 38, and 51 PU's would give a score of 9.4.  It would look under the Male chart, then the age bracket, then find the corresponding number of PU's they did in Column A and give the score.  Attached is an example.
count.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel VBA DeveloperCommented:
Insert this formula below your input cells on Sheet 2:

=OFFSET(Sheet1!\$B\$3,MATCH(B3,Sheet1!\$A\$4:\$A\$104,0),IF(B1="M",0,5)+IF(B2>=70,4,INT(B2/10)-2))

Regards,
-Glenn
Incident ResponderAuthor Commented:
Glenn

I tried to paste the formula but it is giving me a result of 0
Network ManagerCommented:
There seems to be a small flaw in the formula.  I put Female, 16 and 28.  It returns 9.5  when it should be 8.
Incident ResponderAuthor Commented:
Cant seem to get it to work.
Network ManagerCommented:
Here is picture using Glenn's formula in cell B5 on Sheet 2 with your default input.

Incident ResponderAuthor Commented:
Aww i see, I need it the other way      Gender  Age PU, going across, not up and down like the image.
Incident ResponderAuthor Commented:
How do I adjust to make it work the way I need it to.
Excel VBA DeveloperCommented:
I made it work in an example workbook.  Please take a look.

-Glenn
EE-Count.xlsx
Excel VBA DeveloperCommented:
Well, your original example had the metrics in a single column.  See the attached example workbook for both possible solutions (i.e., in a column and in a row).

Regards,
-Glenn
EE-Count.xlsx
Network ManagerCommented:
Glenn,

This is a nice job however I still get 9.5 when I use F/16/28 but using the chart on Sheet 1 that shows it should be 8.  Using the criteria given in the example (M/38/51) it comes back correctly.  I was just doing some spot checking when I found the one from my example.
Excel VBA DeveloperCommented:
Yep, there's an error if the age is less than 20.  Here's the corrected formula for the original sheet layout:

=OFFSET(Sheet1!\$B\$3,MATCH(B3,Sheet1!\$A\$4:\$A\$104,0),IF(B1="M",0,5)+MATCH(B2,{0,30,40,50,60},1)-1)

and for the new layout:
=OFFSET(Sheet1!\$B\$3,MATCH(D2,Sheet1!\$A\$4:\$A\$104,0),IF(B2="M",0,5)+MATCH(C2,{0,30,40,50,60},1)-1)

Updated workbook attached.

-Glenn
EE-Count.xlsx

Experts Exchange Solution brought to you by