Finding Value between two Numbers

[Sarina2424]

Sheet 1
a1: 1 b1: 14 c1: House d1: Balloon
a2: 15 b2: 21 c2: Monkey d2: Banana
a3: 22 b3: 24 c3: Dog d3: Ball

Sheet 2
a1: 1 b1: c1:
a2: 1 b2: c2:
a3: 2 b3: c3:
a4: 6 b4: c4:
a5:17 b5: c5:
a6:23 b6: c6:
a7:24 b7: c7:

Hi I was wonder if anyone can help me with this...using a Macro...I dont want to use formulas...
So I want to look at Sheet 2 to Column A...and look for the particular value in sheet 1 and see if that value is between a1 and b1, if not it looks at if its between a2 and b2, and if its not it looks at if the value is between a3 and b3 and so on...until it finds that value...and then it copies the text in column c and d that corresponds to the matched value and pastes in into column b and c in sheet 2.

So if I want to look at a1 in sheet 2, i find that it is between a1 and b1 in sheet 1...so I will copy c1 and d1 (House, Ballon) and paste it into, b1 and c1 in sheet 2...

Thank you...

[StephenR]

I think it would help people if you could put that in a workbook and upload to the forum.

[Sarina2424]

Sure! here you go...

[StephenR]

Try this. Have taken "between" to include boundary values.

Please Login or Register  to view this content.

[Sarina2424]

Thank you so much! It works! I was wondering, how would I change the formula if in Sheet 1 the range was in column B and C instead of starting in Column A?

[StephenR]

There is a simpler approach, which I deliberately overlooked before... Try this formula in B1 and copied down and across. A1:A3 is the range containing the minimum number for each band.

=LOOKUP($A1,Sheet1!$A$1:$A$3,Sheet1!C$1:C$3)

[Sarina2424]

Thanks it works!