Count lookup from low/high values

**beat** · 02-03-2012, 06:06 AM

Pretty Simple i hope.

Heres a picture that should shed more light

lookup example.jpg

So i want to return the value in G7. It is the count of the range from E7:F7

is vlookup what im after?

Thanks

**Andrew-R** · 02-03-2012, 06:11 AM

I think you want =(MATCH(F7,J:J,0)-MATCH(E7,J:J,0))+1

As an aside, please post example workbooks, not jpeg images - nobody wants to have to retype your data to test their formula.

**Fotis1991** · 02-03-2012, 06:19 AM

Hi

Would you like to test this??

=(F7-E7)*2+1

Hope to helps you.

**beat** · 02-03-2012, 06:28 AM

Thanks,

Sorry it wasn't working before (the up-loader)

Heres the sheet its slightly different from the jpeg and has increments.

Thanks

**beat** · 02-03-2012, 10:56 AM

Still struggling with this formula

**beat** · 02-04-2012, 06:54 AM

Should this be done in VBA then?

**Andrew-R** · 02-05-2012, 03:28 AM

I'm puzzled as to why my original formula didn't work. It seems that adding 0.1 to the previous value returns a number which isn't exactly equal to x+0.1

This formula does work:

=(MATCH(F3,ROUND($J$1:$J$100,1),0)-MATCH(E3,ROUND($J$1:$J$100,1),0))+1

However, this is an array formula, so it must be entered using Ctrl-Shift-Enter, not just enter.

**beat** · 02-06-2012, 05:16 AM

This Works thanks a lot.

I may encounter a problem like so (attached) in the future and it would be great if i could work around it but for now this is great.

The problem in "example 5" is that currently it does not round up.

lookupsteps2.xlsx