IF LOOKUP VALUE IS GREATER THAN ONE NUMBER FROM MULTIPLE MATCHES

[mechrobin]

HI Folks,

I want to get the result as "Shopped" if my lookup value result is greater than 800 from multiple matches. For eg

My lookup values are;

100001-AMR-0004-01

1-NS-62001-2110

Below is my lookup table and if all the values of "last status" colum is greater than 800 then result should be "Shipped"

Engineering Drawing	Last Status
100001-AMR-0004-01	860
100001-AMR-0004-01	860
100001-AMR-0004-01	860
100001-AMR-0004-01	860
1-NS-62001-2110	830
1-NS-62001-2110	783

 

[Peter_SSs]

if all the values of "last status" colum is greater than 800 then result should be "Shipped"

Otherwise the result should be ....?

See if this helps

21 10 09.xlsm
	A	B	C	D	E	F
1	My lookup values are;	Status			Engineering Drawing	Last Status
2	100001-AMR-0004-01	Not Shipped			100001-AMR-0004-01	860
3	1-NS-62001-2110				100001-AMR-0004-01	860
4					100001-AMR-0004-01	860
5					100001-AMR-0004-01	860
6					1-NS-62001-2110	830
7					1-NS-62001-2110	783
Status

Cell Formulas
Range	Formula
B2	B2	=IF(MIN(FILTER(F2:F7,ISNUMBER(MATCH(E2:E7,A2:A3,0)),0))>800,"Shipped","Not Shipped")

 

[mechrobin]

Otherwise the result should be ....?

See if this helps

21 10 09.xlsm
	A	B	C	D	E	F
1	My lookup values are;	Status			Engineering Drawing	Last Status
2	100001-AMR-0004-01	Not Shipped			100001-AMR-0004-01	860
3	1-NS-62001-2110				100001-AMR-0004-01	860
4					100001-AMR-0004-01	860
5					100001-AMR-0004-01	860
6					1-NS-62001-2110	830
7					1-NS-62001-2110	783
Status

Cell Formulas
Range	Formula
B2	B2	=IF(MIN(FILTER(F2:F7,ISNUMBER(MATCH(E2:E7,A2:A3,0)),0))>800,"Shipped","Not Shipped")

Hi Peter,

Thanks for the reply.

But it's not working for me. As you can see from your result, B2 value should be "Shipped" since all values are greater than 800.

 

[Peter_SSs]

OK, I thought you wanted check all the lookup values at once. Try this instead.

21 10 09.xlsm
	A	B	C	D	E	F
1	My lookup values are;	Status			Engineering Drawing	Last Status
2	100001-AMR-0004-01	Shipped			100001-AMR-0004-01	860
3	1-NS-62001-2110	Not Shipped			100001-AMR-0004-01	860
4					100001-AMR-0004-01	860
5					100001-AMR-0004-01	860
6					1-NS-62001-2110	830
7					1-NS-62001-2110	783
Status

Cell Formulas
Range	Formula
B2:B3	B2	=IF(MIN(FILTER(F$2:F$7,ISNUMBER(MATCH(E$2:E$7,A2,0)),0))>800,"Shipped","Not Shipped")

 

[mechrobin]

OK, I thought you wanted check all the lookup values at once. Try this instead.

21 10 09.xlsm
	A	B	C	D	E	F
1	My lookup values are;	Status			Engineering Drawing	Last Status
2	100001-AMR-0004-01	Shipped			100001-AMR-0004-01	860
3	1-NS-62001-2110	Not Shipped			100001-AMR-0004-01	860
4					100001-AMR-0004-01	860
5					100001-AMR-0004-01	860
6					1-NS-62001-2110	830
7					1-NS-62001-2110	783
Status

Cell Formulas
Range	Formula
B2:B3	B2	=IF(MIN(FILTER(F$2:F$7,ISNUMBER(MATCH(E$2:E$7,A2,0)),0))>800,"Shipped","Not Shipped")

I'm So sorry. Don't know whether it's my mistake or not; because I'm getting all the results as "Not Shipped"

This is how I changed the formula.
=IF(MIN(FILTER('[JDE 09.10.2021.xlsb]Sheet1'!$K$1:$K$159294,ISNUMBER(MATCH('[JDE 09.10.2021.xlsb]Sheet1'!$F$1:$F$159294,D3,0)),0))>800,"Shipped","Not Shipped")

 

[Peter_SSs]

Provided ..
- Your lookup value is in D3 of the formula sheet
- Drawing codes are in col F of the JDE workbook
- Status numbers are in col K of the JDE workbook
.. then you have adjusted the formula correctly.
In that case, the likely problem is that the Status numbers are actually text, not numbers. You could check in that JDE workbook sheet with a formula like this
=ISNUMBER(K2)

If that returns FALSE, then try this adjustment to the formula

=IF(MIN(FILTER('[JDE 09.10.2021.xlsb]Sheet1'!$K$1:$K$159294,ISNUMBER(MATCH('[JDE 09.10.2021.xlsb]Sheet1'!$F$1:$F$159294,D3,0)),0)+0)>800,"Shipped","Not Shipped")

 

[mechrobin]

Provided ..
- Your lookup value is in D3 of the formula sheet
- Drawing codes are in col F of the JDE workbook
- Status numbers are in col K of the JDE workbook
.. then you have adjusted the formula correctly.
In that case, the likely problem is that the Status numbers are actually text, not numbers. You could check in that JDE workbook sheet with a formula like this
=ISNUMBER(K2)

If that returns FALSE, then try this adjustment to the formula

=IF(MIN(FILTER('[JDE 09.10.2021.xlsb]Sheet1'!$K$1:$K$159294,ISNUMBER(MATCH('[JDE 09.10.2021.xlsb]Sheet1'!$F$1:$F$159294,D3,0)),0)+0)>800,"Shipped","Not Shipped")

Hi Peter,

Sorry for the delay in reply. This worked.

Thank you

 

[Peter_SSs]

You're welcome. Thanks for letting us know.