# Thread: Combining If... Vlookup... Offset... in a formula

1. ## Combining If... Vlookup... Offset... in a formula

I am trying to look down a list of names and results over a period and put in a table a count of the number of times people have got an end score higher than the first score. Plus Lower than and same as in different counts.

My brain now hurts and I ask if someone can help please?

I have attached a copy.

Thanks

Peter

2. I don't think it can be done directly with formulas but would require an intermediate setup. I created a pivot table and then got the results from the pivot table

Steve

3. Thanks Steve
looks good, but I need to compare the last result, which may be Closing, Review1, 2 or 3.

What you've done certainly gets me thinking thanks.

4. In G5 you can enter:
=MATCH(9E+300,C5:F5)

and copy G5 from G6 to G34, this gives the last entry for that name. Then change the formulas in I2:K2 from Col C to Col G...

Steve

5. Thanks again Steve

I have it now. Please tell me what is the 9E+300?

6. 9E+300 is just a very large number (9 followed by 300 zeroes). The match "trick"/technique used tries to find this number and when it can't it quits at the end of the list: the last number in the range.

Steve

7. Thanks Steve for your help.

Peter

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•