Formula help to find a word and then search if a number is within a range

[explosive_duck]

Hi,

I am trying to find a formula to search for a word and then check if a number is within a range and then bring back the corresponding value.

As an example:

I have the below table of information below

[TABLE="width: 256"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl69, width: 64"]CATEGORY[/TD]
[TD="class: xl70, width: 64"]MIN[/TD]
[TD="class: xl70, width: 64"]MAX[/TD]
[TD="class: xl70, width: 64"]TARGETS[/TD]
[/TR]
[TR]
[TD="class: xl66"]T-SHIRT[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]5[/TD]
[TD="class: xl67"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]T-SHIRT[/TD]
[TD="class: xl67"]6[/TD]
[TD="class: xl67"]16[/TD]
[TD="class: xl67"]4[/TD]
[/TR]
[TR]
[TD="class: xl66"]SHIRT[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]4[/TD]
[TD="class: xl67"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]SHIRT[/TD]
[TD="class: xl67"]5[/TD]
[TD="class: xl67"]20[/TD]
[TD="class: xl67"]3[/TD]
[/TR]
[TR]
[TD="class: xl66"]JEANS[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]4[/TD]
[TD="class: xl67"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]JEANS[/TD]
[TD="class: xl67"]5[/TD]
[TD="class: xl67"]7[/TD]
[TD="class: xl67"]3[/TD]
[/TR]
[TR]
[TD="class: xl66"]JEANS[/TD]
[TD="class: xl67"]8[/TD]
[TD="class: xl67"]25[/TD]
[TD="class: xl67"]4[/TD]
[/TR]
</tbody>[/TABLE]

And I have to find the below criteria below and fill in the corresponding target column (?)

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl68, width: 64"]CATEGORY[/TD]
[TD="class: xl68, width: 64"]ORDER[/TD]
[TD="class: xl68, width: 64"]TARGET[/TD]
[/TR]
[TR]
[TD="class: xl66"]T-SHIRT[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]?[/TD]
[/TR]
[TR]
[TD="class: xl66"]JEANS[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]?[/TD]
[/TR]
[TR]
[TD="class: xl66"]JEANS[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]?[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"] [/TD]
[/TR]
</tbody>[/TABLE]


If anyone can help me with the below then that would be great.

Thanks,

 

[Sinon]

If I understand it, you want to return the value in TARGET for t-shirt with the ORDER between MIN and MAX. So, for t-shirt with order 3 you want to return value 2? If so, Try using an INDEX/MATCH as an array:
assuming your first table is in columns A:D and your second is in columns J:L, then add this in L2:
{=INDEX(D:D,MATCH(1,(J2=A:A)*(K2>=B:B)*(K2<=C:C),0))}
Do not add the curly braces manually but confirm the formula as an array with CTRL+SHIFT+ENTER

If I misunderstood your requirements please provide an example.

 

[a_songaila]

I not sure if you are loking for that but try this.

=SUMIFS(TARGET COLUMN, CATEGORY COLUMN, "T-SHIRT")

Try this, the answer should be 6, correct?

 

[explosive_duck]

If I understand it, you want to return the value in TARGET for t-shirt with the ORDER between MIN and MAX. So, for t-shirt with order 3 you want to return value 2? If so, Try using an INDEX/MATCH as an array:
assuming your first table is in columns A:D and your second is in columns J:L, then add this in L2:
{=INDEX(D:D,MATCH(1,(J2=A:A)*(K2>=B:B)*(K2<=C:C),0))}
Do not add the curly braces manually but confirm the formula as an array with CTRL+SHIFT+ENTER

If I misunderstood your requirements please provide an example.

Hi Sinon, that is exactly what I am looking for. Thank you so much for your help!

 

[Sinon]

Glad I could help