Excel 2007 : Some Form Of VLookup type function....

There are gaps between the intervals - will the values always fit in - ie do you only need to check the "from" value or do you need to also check "to"?

If it's only a case of checking the "from" values, the following formula should work but you need to sort the lookup area first.

Please Login or Register  to view this content.

in cell D2 and copied down.

Firstly, thank you so much for the reply.... It is really appreciated!

If it is possiable, I really need to match on Hole (as there will be more than 1 hole in the SS), and on from and to.....

Karl

A few questions:

how many types of hole will you have?

will the ranges be the same for each type?

what happens if a hole has a "from" value that fits a range but the "to" value is outside it?

There are about 400 holes in the series.

Ranges will vary between holes. Ie Seam B will be from 40 to 50 in one hole and 20 to 30 in another.

If the from matches and the To doesnt, then if possiable i would like it to report the value which matches the from statement.....

If the from matches and the To doesnt, then if possiable i would like it to report the value which matches the from statement.....

If that's the case, you only need to look up the "from" values and the hole type.
I would add a helper column (N) which combines the hole type and the "from" value

Please Login or Register  to view this content.

and use this formula to return the result

Please Login or Register  to view this content.

Note - the values in the lookup table must be in ascending order.