Look for value based on multiple ranges

[MagsinoAS]

Anyone can help me with this, I need to get a result based on ranges. The column criteria would be range of weights while the row is range based on postal codes. Please see the screenshot below:

 

[theBardd]

You could try this array formula

=INDEX($B$2:$D$4,
MATCH(1,(A9>=--LEFT($A$2:$A$4,FIND("-",$A$2:$A$4)-1))*(A9<=--MID($A$2:$A$4,FIND("-",$A$2:$A$4)+1,99)),0),
MATCH(1,(B9>=--LEFT($B$1:$E$1,FIND("-",$B$1:$E$1)-1))*(B9<=--SUBSTITUTE(MID($B$1:$E$1,FIND("-",$B$1:$E$1)+1,99),"kg","")),0))

 

[FormR]

If you could change your lookup table similar to below then you could try this:

Excel 2013
	A	B	C	D	E
1		0.000	0.501	1.010	2.010
2	2000	5.66	6.27	7.27	8
3	2235	7.31	8.31	9.31	10
4	3000	5.87	6.45	7.34	9
5
6
7
8	2000	0.60	6.27
9	3001	2.00	7.34
10	2998	0.50	7.31
Sheet1

Cell Formulas
Range	Formula
C8	=INDEX($B$2:$E$4,MATCH($A8,$A$2:$A$4),MATCH($B8,$B$1:$E$1))

 

[MagsinoAS]

It works if the weight is 0.501+, I'm getting error if I enter a weight between 0 to 0.5KG.

Thanks =)

 

[MagsinoAS]

Great, this one will work for me. Thank you so much.

 

[bosco_yip]

Follow OP's 1st level setup,

B8 formula, copy down :

=LOOKUP(A8,--LEFT(A$2:A$4,FIND("-",A$2:A$4)-1),INDEX(B$2:E$4,0,MATCH(B8,INDEX(--LEFT(B$1:E$1,FIND("-",B$1:E$1)-1),0))))

Edit : The OP's table in E1 is mistake, should read as : "2.01-3kg"

Regards

 

[MagsinoAS]

Thanks Bosco-_yip...ur help is highly appreciated =)