Value between 2 values

[kkirank.kmp]

Hi I'm a newbie to writing macro, so could someone please help me with
this. I have 2 sheets in a workbook. Sheet 1 looks like this
Column A Column B Column C
ABCDE 3000 3099
BDSCXD 4500 4599
UIT 4800 5199
SWEDFE 5300 5399
SmartS 5450 5499
XYZ123 5600 5629
QWER23 5650 5669


Sheet2 looks like this

Column A Column B Column C
4523 12345
5621 34543
4545 43534
5326 23453
5629 23432

I need to check the data in Column B (in Sheet 2) above and compare it
with Sheet 1, i.e., see if it lies between Column B & Column C (in
Sheet 1). For example: "4523" is the first value in Sheet 2, I need to
see which row 4523 belongs in Sheet 1, i.e., 4523 lies between 4500 &
4599 (row2 in Sheet1), when it finds the row I need to pick up the
name in Column A and put it in Column A of Sheet2. Another example the
last value is 5629 and it lies between 5600 & 5629. So basically
Column B in Sheet2 is a data that lies between a start and end value
(ColumnB & columnC in sheet1).
The result will look like this in Sheet2

Column A Column B Column C
BDSCXD 4523 12345
XYZ123 5621 34543
BDSCXD 4545 43534
SWEDFE 5326 23453
XYZ123 5629 23432

I don't know if this is of importance, their are more than 15000 rows
in Sheet2.

Thanks in advance for any help!

 

[Bob Phillips]

You do not need a macro, a simple formula does it,.

In A1 of Sheet2

=INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,1))

and copy down.

--
---
HTH

Bob


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

 

[kkirank.kmp]

Hi Bob,
Question, I'm not sure of this, but the data in Sheet2 is generated
by another process (macro), so the number of rows are not known, so
this needs to be done automatically,

Thanks

 

[kkirank.kmp]

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

 

[kkirank.kmp]

To you above solution, I need something like this
=INDEX(Sheet1!A:A,MATCH(B1,(B1>=Sheet1!B:B) & (B1<=Sheet1!C:C),
1))....I know this does not work, but just wanted to give something

 

[Bob Phillips]

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)

 

[kkirank.kmp]

Hi Bob,
I have this spreadsheet that I can send you, I do not see any place
where I can attach it. If you could provide me with a email id I can
send it to u.

Thanks,

 

[kkirank.kmp]

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

 

[Bob Phillips]

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)