# Formula Help - Vlookup and Match?

#### Bigtime

##### New Member
Hi,

I can't figure out how to go about writing a formula for this.

https://1drv.ms/x/s!AjE7rOJOD5SS0A56EdcLfmKnJeMO

At the top is some sample data collected from students taking a pre and post fitness assessment. If they did both the pre and the post, they receive 25 points. If they did only one, they receive 0 points. I also need to get the average between the pre and post test.

Any help is much appreciated!

Thanks,

Tim

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### etaf

##### Well-known Member
for the points you can use
=IF(COUNTIFS(\$F\$2:\$F\$6,A12,\$A\$2:\$A\$6,"pre")+COUNTIFS(\$F\$2:\$F\$6,A12,\$A\$2:\$A\$6,"post")=2,25,IF(COUNTIFS(\$F\$2:\$F\$6,A12,\$A\$2:\$A\$6,"pre")+COUNTIFS(\$F\$2:\$F\$6,A12,A\$2:\$A\$6,"post")=1,0,0))
BUT that can be simplified , as i assume if they have done none , they wont be on the list
so change to
=IF(COUNTIFS(\$F\$2:\$F\$6,A12,\$A\$2:\$A\$6,"pre")+COUNTIFS(\$F\$2:\$F\$6,A12,\$A\$2:\$A\$6,"post")=2,25,0)

for the crunches , only applies if they have done a pre and a post so use
=IF(COUNTIFS(\$F\$2:\$F\$6,A12,\$A\$2:\$A\$6,"pre")+COUNTIFS(\$F\$2:\$F\$6,A12,\$A\$2:\$A\$6,"post")=2,AVERAGEIFS(\$H\$2:\$H\$6,\$F\$2:\$F\$6,A12),"")

#### Bigtime

##### New Member
Thank you very much! Worked perfectly!!!

#### Anand Sharma

##### Board Regular
you can also use sumproduct here to shorten you code e.g.

=IF((SUMPRODUCT(--(\$A\$1:\$A\$5="Pre"),--(\$C\$1:\$C\$5=A12))+SUMPRODUCT(--(\$A\$1:\$A\$5="Post"),--(\$C\$1:\$C\$5=A12)))>1,25,0)

#### Bigtime

##### New Member
Thank you Anand!!

#### Bigtime

##### New Member
How would I integrate into the formula a way to calculate % increase from pre to post test on the crunches?

#### etaf

##### Well-known Member
i would use
=(INDEX(\$H\$2:\$H\$6,MATCH("pre"&A12,\$A\$2:\$A\$6&\$F\$2:\$F\$6,0))-INDEX(\$H\$2:\$H\$6,MATCH("post"&A12,\$A\$2:\$A\$6&\$F\$2:\$F\$6,0)))/INDEX(\$H\$2:\$H\$6,MATCH("pre"&A12,\$A\$2:\$A\$6&\$F\$2:\$F\$6,0))
and enter using Control + Shift + Enter to get {} around the formula

this is the number on Pre - the number on Post divided by the number on Pre

OR
to avoid using an array formula {}
you can just use this
=((LOOKUP(2,1/(\$A\$2:\$A\$6="pre")/(\$F\$2:\$F\$6=A12),\$H\$2:\$H\$6))-(LOOKUP(2,1/(\$F\$2:\$F\$6=A12)/(\$F\$2:\$F\$6=A12),\$H\$2:\$H\$6)))/(LOOKUP(2,1/(\$A\$2:\$A\$6="pre")/(\$F\$2:\$F\$6=A12),\$H\$2:\$H\$6))
as is

Last edited:

Thank you!!!!!!!

you are welcome

Replies
36
Views
2K
Replies
12
Views
882
Replies
20
Views
861
Replies
1
Views
688
Replies
12
Views
923

1,190,864
Messages
5,983,279
Members
439,836
Latest member
BuckyBoyRx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back