Well I tested it and I got exactly the results you showed, and it does
exactly what you describe below. So what are you getting?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
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