Look for value based on multiple ranges

Re: Look for value based on multiple ranges

Hi MagsinoAS,
welcome to the forum
A table of the data will help obtain a quicker result than a Print Screen of your worksheet

Re: Look for value based on multiple ranges

eg
[table="class:thin_grid"]

[tr][td]

v

[/td]

[td="bgcolor:#ECF0F0, align:center,width:140"]A[/td]
[td="bgcolor:#ECF0F0, align:center,width:57"]B[/td]
[td="bgcolor:#ECF0F0, align:center,width:71"]C[/td]
[td="bgcolor:#ECF0F0, align:center,width:65"]D[/td]
[td="bgcolor:#ECF0F0, align:center,width:64"]E[/td]

[/tr][tr]

[td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]Weight/Postal Code[/COLOR][/td]
[td="bgcolor:#1F497D, align:LEFT"][COLOR="#FFFFFF"]0-0.5kg[/COLOR][/td]
[td="bgcolor:#1F497D, align:LEFT"][COLOR="#FFFFFF"]0.501-1kg[/COLOR][/td]
[td="bgcolor:#1F497D, align:LEFT"][COLOR="#FFFFFF"]1.01-2kg[/COLOR][/td]
[td="bgcolor:#1F497D, align:LEFT"][COLOR="#FFFFFF"]3.01-4kg[/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#1F497D, align:LEFT"][COLOR="#FFFFFF"]2000-2234[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$5.66[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$6.27[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$7.27[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$8.00[/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#1F497D, align:LEFT"][COLOR="#FFFFFF"]2235-2999[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$7.31[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$8.31[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$9.31[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$10.00[/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#1F497D, align:LEFT"][COLOR="#FFFFFF"]3000-3207[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$5.87[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$6.45[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$7.34[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]$9.00[/COLOR][/td]

[/tr]

[/table]

and
[table="class:thin_grid"]

[tr][td]

v

[/td]

[td="bgcolor:#ECF0F0, align:center,width:140"]A[/td]
[td="bgcolor:#ECF0F0, align:center,width:57"]B[/td]
[td="bgcolor:#ECF0F0, align:center,width:71"]C[/td]

[/tr][tr]

[td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFF00, align:LEFT"][COLOR="#000000"]Desired Result[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"][/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"][/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]Post Code[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]Weight[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:LEFT"][COLOR="#000000"]Result Cost[/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]8[/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2000[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]0.60[/COLOR][/td]
[td="bgcolor:#FFFF00, align:RIGHT"][COLOR="#000000"][/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]9[/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]3001[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2.00[/COLOR][/td]
[td="bgcolor:#FFFF00, align:RIGHT"][COLOR="#000000"][/COLOR][/td]

[/tr]

[tr]

[td="bgcolor:#ECF0F0, align:center"]10[/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]2998[/COLOR][/td]
[td="bgcolor:#FFFFFF, align:RIGHT"][COLOR="#000000"]0.50[/COLOR][/td]
[td="bgcolor:#FFFF00, align:RIGHT"][COLOR="#000000"][/COLOR][/td]

[/tr]

[/table]

Re: Look for value based on multiple ranges

Hi
Used a bit of custom format trickey to mask the post code value ranges and use
[COLOR="#0000CD"]=INDEX($B$2:$E$4,MATCH(TRUE,INDEX($A$2:$A$4>=MIN(MAX($A$2:$A$4),A8),0),0),MATCH(TRUE,INDEX($B$1:$E$1>=MIN(MAX($B$1:$E$1),B8),0),0)[/COLOR]