Formula for two columns with multiple ranges

[exceleratevba]

I have a table with two columns that have rows with various ranges. What formula would I need to use to have my values in Column A to check if they are within the ranges in columns B and C.

I'm looking to have each cell with a value check if it falls between any of the ranges in the columns and return a Yes or No

The issue I'm running into is that values have 10 digits and the range values have 9 digits.

[TABLE="width: 437"]
<tbody>[TR]
[TD][TABLE="width: 437"]
<tbody>[TR]
[TD][TABLE="width: 437"]
<tbody>[TR]
[TD]HS Codes(VALUES)[/TD]
[TD]HS Range From[/TD]
[TD]HS Range To[/TD]
[TD]EC18 Statement[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]102310000[/TD]
[TD]102390000[/TD]
[TD] Yes or NO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]105151000[/TD]
[TD]105159000[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]106110000[/TD]
[TD]106900090[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]207601100[/TD]
[TD]208900090[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]210910000[/TD]
[TD]210930000[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]210999000[/TD]
[TD]210999000[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]301110000[/TD]
[TD]301190000[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]301920000[/TD]
[TD]301920000[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]301990010[/TD]
[TD]301990090[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col span="2"></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col span="2"></colgroup>[/TABLE]

 

[jtakw]

Hi,

Obviously, a 10 digit number will Never be within 2 Nine digit numbers, so I'm assuming you're comparing only Part of the number in Column A (HS Codes), so which part are we using or ignoring?

 

[exceleratevba]

I'm going through the columns and updating the digits to 10. What about now?


[TABLE="class: cms_table, width: 437"]
<tbody>[TR]
[TD] Codes(VALUES)[/TD]
[TD]HS Range From[/TD]
[TD]HS Range To[/TD]
[TD]EC18 Statement[/TD]
[TD]Formula[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 437"]
<tbody>[TR]
[TD]9403609090[/TD]
[TD]9307000000[/TD]
[TD]9307000000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9401691000[/TD]
[TD]9401699000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9401900030[/TD]
[TD]9401900030[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9403300010[/TD]
[TD]9403609090[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9504200000[/TD]
[TD]9504200000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9508100011[/TD]
[TD]9508100020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9601100000[/TD]
[TD]9601900000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9614001100[/TD]
[TD]9614001100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9615190000[/TD]
[TD]9615190000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9703000000[/TD]
[TD]9703000000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9705000000[/TD]
[TD]9706000090[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9403609090[/TD]
[TD]9897000000[/TD]
[TD]9897000000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col span="2"></colgroup>[/TABLE]

 

[jtakw]

Hi,

In your latest sample, only 1 of the Column A values are in the ranges in Columns B & C, so, hope I understand correctly:

Excel 2010
	A	B	C	D
1	Codes(VALUES)	HS Range From	HS Range To	EC18 Statement
2	9403609090	9307000000	9307000000	No
3	9403609090	9401691000	9401699000	No
4	9403609090	9401900030	9401900030	No
5	9403609090	9403300010	9403609090	Yes
6	9403609090	9504200000	9504200000	No
7	9403609090	9508100011	9508100020	No
8	9403609090	9601100000	9601900000	No
9	9403609090	9614001100	9614001100	No
10	9403609090	9615190000	9615190000	No
11	9403609090	9703000000	9703000000	No
12	9403609090	9705000000	9706000090	No
13	9403609090	9897000000	9897000000	No
Sheet22

Cell Formulas
Range	Formula
D2	=IF(AND(A2>=B2,A2<=C2),"Yes","No")

D2 formula copied down.