index match and dynamic subtraction

member23 · Aug 23, 2019

Hello, I would really appreciate some help please

What I want to do is search for A2 in column F:F and for B2 to be >= in G:G. So, 2 variables. Then, if these two variables are met I want to substact the 5 items that are required in C2, from H2 and H3 (5 items in total) and show somehow where it got them from. When A3 does the same, it should appear as an error because no items are left to get although criteria is met.

I tried to do this with INDEX MATCH but failed when I got to the incorporation of subtraction phase let alone the section that needs to show where these items were taken from.
Thanks.

[FONT=&quot] [/FONT]	[FONT=&quot]A[/FONT]	[FONT=&quot]B[/FONT]	[FONT=&quot]C[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]E[/FONT]	[FONT=&quot]F[/FONT]	[FONT=&quot]G[/FONT]	[FONT=&quot]H[/FONT]
1	product name	date	items required		something else	product name	date of production	in stock
2	SISJD	3/1/2019	5		12448	SISJD	30/9/2015	3
3	SISJD	3/1/2019	2		69841	SISJD	30/9/2015	2
4	SISJD	3/1/2019	2		54985414	HJHJ	30/9/2015	1
5	XVVDG	22/2/2019	1		547941	GHGJ	30/5/2016	1
6	XCVF	3/1/2019	1

sergioMabres · Aug 23, 2019

Hi M23,
Here is an idea:

product name	date	items required	Stock - Required	something else	product name	date of production	in stock
SISJD	1/3/2019	5	-2	12448	SISJD	9/30/2015	3
SISJD	1/3/2019	2	1	69841	SISJD	9/30/2015	2
SISJD	1/3/2019	2	1	54985414	HJHJ	9/30/2015	1
XVVDG	2/22/2019	1	ERROR	547941	GHGJ	5/30/2016	1
XCVF	1/3/2019	1	ERROR

Where D2 is =IFERROR(IF(VLOOKUP(A2,$G$2:$I$5,2,0)<=B2,VLOOKUP(A2,$G$2:$I$5,3,0)-C2,"ERROR"),"ERROR")

To have a full solution you need a Procurement System, the reason is that item SISJD could be in several locations so you need to
Add all stock for SISJD in one place and inform all location for SISJD that you have to pick list
Then the second request for SISJD has to take into account the request before to have a First asked First Served queue

Here is the test file https://1drv.ms/x/s!AovCE1fDrrdSnUqZ99GrbXgR9q5h?e=ozqaBK

Cheers
Sergio

member23 · Aug 24, 2019

Thank you so much Sir for spending time and answering my question. All the best.

index match and dynamic subtraction

member23

New Member

Excel Facts

sergioMabres

Well-known Member

member23

New Member

Similar threads

Forum statistics

Share this page

index match and dynamic subtraction

member23

New Member

Excel Facts

sergioMabres

Well-known Member

member23

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock