The data on sheet1 has to be ordered, as you showed in your original example
for it to work.So sort on column B.

If the data cannot be ordered, this array formula works

=INDEX(Sheet1!$A$1:$A$200,MATCH(1,(B1=Sheet1!$B$1 :$B$200)*(B1<=Sheet1!$C$1:$C$200),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.


--
---
HTH

Bob


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



wrote in message
...
Hi Bob,
Never mind here I have copied the data
Hi, Thanks for the reply. I also used the same thing, but did not work
for some cells. Here is the test data that 'm using on Sheet1
ABCDE 3000 3099
BDSCXD 4500 4599
UIT 4800 5199
SWEDFE 5300 5399
SmartS 5450 5499
XYZ123 5600 5629
QWER23 5650 5669
342222 5800 5899
Test123 5900 6330
1223324 6420 6499
65435 6360 6419
wqewwq 6700 6999
fgf 9101 9195
retreg 9201 9219
erw34def 9319 9331

Data on Sheet2 after applying the formula
QWER23 5663
342222 5830
342222 5830
342222 5831
342222 5838
Test123 6005
Test123 6399
Test123 6399
Test123 6399
Test123 6399
Test123 6400
Test123 6400
Test123 6400
Test123 6400
Test123 6400
Test123 6400
Test123 6404
Test123 6404
Test123 6404
Test123 6404
Test123 6404
65435 6429
65435 6429
wqewwq 6751

So where u see 6429, I should have had "1223324" instead of "65435",
Similar for 6404, it should have been "65435" instead of "Test123".
Any Suggesstion, why I get wrong results

Thanks