Hi Bob,
The above solution you gave me works only if I have the exact
value. This is what I need to do I need to take the value from B1 in
Sheet2 and see within what range it lies in Sheet1. The range in
Sheet1 is values between ColB & colC. Basically I need to see if B1 in
Sheet2 lies between, B1 & C1 in Sheet1, if not see if it lies between
B2 & C2 and so forth. So if I look at the first value in Sheet2 it is
4523 (B1). I need to see if 4523 lies between B1 & C1 in Sheet1 (that
would be 3000 & 3099), which is not, so I go to the next row in Sheet2
(4500 & 4599) which is true, so I need to get the value from A2 in
Sheet1 and put it in A1 in Sheet2 (as we are looking at value in B1)

Thanks