Assign value to cell based on range in another sheet

[marceticd]

Hello,can someone help on this issue:


I have two Sheets:
1. contains samples with number assigned for each sample
2. contains boxes with range of numbers for each box (start/end number)


I need to assign box number to each sample based on sample number and box number range


See attached Excel file


thank you.

Sheet1

[TABLE="width: 500"]
<tbody>[TR]
[TD]sample name[/TD]
[TD]sample number[/TD]
[TD]Box Number[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]Sample1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 105"]
<tbody>[TR]
[TD="class: xl65, width: 105"]102[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]assign here box number based on sample number and box start/end range, e.g Box1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]Sample2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 105"]
<tbody>[TR]
[TD="class: xl65, width: 105"]505[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]Sample3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 105"]
<tbody>[TR]
[TD="class: xl65, width: 105"]121[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]Sample4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 105"]
<tbody>[TR]
[TD="class: xl65, width: 105"]374[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Box number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Box1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]100[/TD]
[TD]299[/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Box2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]300[/TD]
[TD]499[/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Box3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]500[/TD]
[TD]799[/TD]
[/TR]
</tbody>[/TABLE]

 

[shaftandspanner]

Hi marceticd

Try this:

Reverse the order of sheet 2 so that the highest End value is at the top of the list - you can then use the lookup function:

In the first box number cell on sheet 1 (I'm assuming this is C2), paste this formula:

=LOOKUP(-B2,-Sheet2!$C$2:$C$4,Sheet2!$A$2:$A$4)

B2 is the sample number
Sheet2!$C$2:$C$4 is the list of end values in decending order
Sheet2!$A$2:$A$4 is the list of box numbers

Then copy this formula down for all of the other rows in sheet 1