Vlookup, Index & Match - Grab values and compare

[cybid]

Greetings to all.
First of all, I am new to this forum and need the help of your excel knowledge.

I have a workbook with the Sheets: A, B, C. On the B sheet I am collecting data from sheet A, and trying to collect other data from sheet C. Since the VLOOKUP wont collect negative col_index_num, I am trying to find a solution with index and match formula, but can't manage it. I am posting 2 images to give a visual of my problem. If you need other info or more details, please let me know and maybe I can upload the document for you. As you can see on the image number 2 I need to collect data with blue circle- only the value next to the 'direct:' text.

Thnx in advance.

 

[jasonb75]

Product B is not properly visible in the screen capture so I can't see the relativity of the data.

 

[cybid]

WASTE 3.0-TEST - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	RAW MATERIALS
2	Kodi	T	Totali	Produkti		Kodi	T	Totali	Produkti		Kodi	T	Totali	Produkti
3	101	11.49	0,616	product a		139	5	0			184	0	0
4	102	5.59	0,181	product b		140	2	0			185	0	0
5	103	5.67	0,443	product c		142	92	0			186	0	0
6	104	6.86	0,000	product d		143	4.075	0,000			187	0	0
7	105	0.02	0,000			144	0.586	0,000			188	0	0
8	106	0.03	0,000			145	24	0			189	0	0
9	107	81.30	0,000			146	0.06	0,000			192	0	0
10	108	63.00	8	product 1		147	0.12	0,000			193	0	0
11	109	42.00	5	product 2		148	0.03	0,000			240	0	0
12	110	19.00	0			149	0.17	0,000			245	0	0
13	111	2.00	0			150	3	0			273	0	0,000
14	112	4.00	0			151	0	0,000			274	0	0,000
15	113	3.00	0			152	0	0,000			275	0	0
16	114	7.00	0			158	26.358	0,000			277	0	0
17	115	2.00	0			159	94	0			278	0	0
18	116	2.00	0			160	139	0			279	0	0
19	117	33.00	0			161	71	0			281	0	0
20	118	79.00	0			162	74	0			282	0	0
21	120	13.00	0			163	27	0			283	0	0
22	121	0.00	0,000			165	0	0			284	0	0
23	122	22.00	0,000			166	0	0			285	0	0
24	123	0.43	0,000			167	0	0			287	0	0
25	127	19.00	0,000			168	0	0			288	0	0
26	128	0.94	0,000			171	0	0			289	0	0
27	129	1.08	0,000			172	0	0			290	0	0
28	130	8.00	0			173	0	0			291	0	0
29	131	258.00	0,000			174	0	0			296	0	0
30	132	0.03	0			175	0	0			297	0	0
31	133	112.00	0			176	0	0			298	0	0
32	134	9.00	0			177	0	0			299	0	0
33	135	27.00	0			178	0	0			300	0	0
34	136	2.00	0			179	0	0			301	0	0
35	138	6.00	0			180	0	0			301	0	0
PRINT

Cell Formulas
Range	Formula
B3:B35	B3	=IFERROR(INDEX(WasteTOSHIBA!$P$1:$P$300,MATCH(A3,WasteTOSHIBA!$A$5:$A$304,FALSE)),0)
C3	C3	=RAWMATERIAL2.0!$B$23
C4	C4	=RAWMATERIAL2.0!$B$24
C5	C5	=RAWMATERIAL2.0!$B$25
C6	C6	=RAWMATERIAL2.0!$B$26
C7	C7	=RAWMATERIAL2.0!$B$27
C8	C8	=RAWMATERIAL2.0!$B$28
C9	C9	=RAWMATERIAL2.0!$B$29
C10	C10	=RAWMATERIAL2.0!$B$3
C11	C11	=RAWMATERIAL2.0!$B$4
C12	C12	=RAWMATERIAL2.0!$B$13
C13	C13	=RAWMATERIAL2.0!$B$14
C14	C14	=RAWMATERIAL2.0!$B$15
C15	C15	=RAWMATERIAL2.0!$B$16
C16	C16	=RAWMATERIAL2.0!$B$17
C17	C17	=RAWMATERIAL2.0!$B$6
C18	C18	=RAWMATERIAL2.0!$B$7
C19	C19	=RAWMATERIAL2.0!$B$8
C20	C20	=RAWMATERIAL2.0!$B$9
C21	C21	=RAWMATERIAL2.0!$B$10
C22	C22	=RAWMATERIAL2.0!$B$30
C23	C23	=RAWMATERIAL2.0!$B$31
C24	C24	=RAWMATERIAL2.0!$B$32
C25	C25	=RAWMATERIAL2.0!$B$33
C26	C26	=RAWMATERIAL2.0!$B$34
C27	C27	=RAWMATERIAL2.0!$B$35
C28	C28	=RAWMATERIAL2.0!$B$51
C29	C29	=RAWMATERIAL2.0!$B$36
C30	C30	=RAWMATERIAL2.0!$B$52
C31	C31	=RAWMATERIAL2.0!$B$50
C32	C32	=RAWMATERIAL2.0!$B$59
C33	C33	=RAWMATERIAL2.0!$B$60
C34	C34	=RAWMATERIAL2.0!$B$61
C35	C35	=RAWMATERIAL2.0!$B$62
L4:L35,G3:G35	G3	=IFERROR(VLOOKUP(F3,WasteTOSHIBA!$A$9:$W$300,16,FALSE),0)
H3	H3	=RAWMATERIAL2.0!$B$63
H4	H4	=RAWMATERIAL2.0!$B$65
H5	H5	=RAWMATERIAL2.0!$B$53
H6	H6	=RAWMATERIAL2.0!$B$44
H7	H7	=RAWMATERIAL2.0!$B$45
H8	H8	=RAWMATERIAL2.0!$B$56
H9	H9	=RAWMATERIAL2.0!$B$38
H10	H10	=RAWMATERIAL2.0!$B$39
H11	H11	=RAWMATERIAL2.0!$B$40
H12	H12	=RAWMATERIAL2.0!$B$43
H13	H13	=RAWMATERIAL2.0!$B$57
H14	H14	=RAWMATERIAL2.0!$B$41
H15	H15	=RAWMATERIAL2.0!$B$42
H16	H16	=RAWMATERIAL2.0!$B$37
H17	H17	=RAWMATERIAL2.0!$B$12
H18	H18	=RAWMATERIAL2.0!B18
H19	H19	=RAWMATERIAL2.0!$B$93
H20	H20	=RAWMATERIAL2.0!$B$94
H21	H21	=RAWMATERIAL2.0!$B$95
H22	H22	=RAWMATERIAL2.0!$B$72
H23	H23	=RAWMATERIAL2.0!$B$73
H24	H24	=RAWMATERIAL2.0!$B$74
H25	H25	=RAWMATERIAL2.0!$B$75
H26	H26	=RAWMATERIAL2.0!$B$77
H27	H27	=RAWMATERIAL2.0!$B$79
H28	H28	=RAWMATERIAL2.0!$B$99
H29	H29	=RAWMATERIAL2.0!$B$101
H30	H30	=RAWMATERIAL2.0!$B$102
H31	H31	=RAWMATERIAL2.0!$B$70
H32	H32	=RAWMATERIAL2.0!$B$96
H33	H33	=RAWMATERIAL2.0!$B$97
H34	H34	=RAWMATERIAL2.0!$B$98
H35	H35	=RAWMATERIAL2.0!$B$84
L3	L3	=IFERROR(VLOOKUP(K3,PRODUCTMIX!$A$9:$W$300,16,FALSE),0)
M3	M3	=RAWMATERIAL2.0!$B$87
M4	M4	=RAWMATERIAL2.0!$B$88
M5	M5	=RAWMATERIAL2.0!$B$89
M6	M6	=RAWMATERIAL2.0!$B$90
M7	M7	=RAWMATERIAL2.0!$B$91
M8	M8	=RAWMATERIAL2.0!$B$103
M9	M9	=RAWMATERIAL2.0!$B$5
M10	M10	=RAWMATERIAL2.0!$B$47
M11	M11	=RAWMATERIAL2.0!$B$54
M12	M12	=RAWMATERIAL2.0!$B$55
M13	M13	=RAWMATERIAL2.0!$B$46
M14	M14	=RAWMATERIAL2.0!$B$48
M15	M15	=RAWMATERIAL2.0!$B$92
M16	M16	=RAWMATERIAL2.0!$B$67
M17	M17	=RAWMATERIAL2.0!$B$68
M18	M18	=RAWMATERIAL2.0!$B$69
M19	M19	=RAWMATERIAL2.0!$B$66
M20	M20	=RAWMATERIAL2.0!$B$80
M21	M21	=RAWMATERIAL2.0!$B$81
M22	M22	=RAWMATERIAL2.0!$B$82
M23	M23	=RAWMATERIAL2.0!$B$11
M24	M24	=RAWMATERIAL2.0!$B$64
M25	M25	=RAWMATERIAL2.0!$B$76
M26	M26	=RAWMATERIAL2.0!$B$71
M27	M27	=RAWMATERIAL2.0!$B$78
M28	M28	=RAWMATERIAL2.0!B58
M29	M29	=RAWMATERIAL2.0!$B$104
M30	M30	=RAWMATERIAL2.0!$B$105
M31	M31	=RAWMATERIAL2.0!$B$106
M32	M32	=RAWMATERIAL2.0!$B$107
M33	M33	=RAWMATERIAL2.0!$B$108
M34	M34	=RAWMATERIAL2.0!$B$58
M35	M35	=RAWMATERIAL2.0!$B$19

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H18	Cell Value	>0	text	NO
M3:M35	Cell Value	>0	text	NO
C4:C35	Cell Value	>0	text	NO
C3,H3:H17,H19:H37	Cell Value	>0	text	NO

WASTE 3.0-TEST - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2					11.04.2021				22:37:44			Store:								page:		1 / 5
3
4					Report 5.3: Total waste report
5
6		date: 01.04.2021 - 10.04.2021
7
8																				amount
9	rawmaterial no.						name									quantity			( )
10
11												direct:				11.4890
12
13												by PLU:				5.4740
14
15	101						Product A					total:				16.9630			(1,1012)		18.68
16
17												direct:				5.5870
18
19												by PLU:				3.5960
20
21	102						Product B					total:				9.1830			(2,0549)		18.87
22
23												direct:				5.6720
24
25												by PLU:				1.1840
26
27	103						Product C					total:				6.8560			(0,6521)		4.47
28
29												by PLU:				0.0160
30
31	104						Product D					total:				0.0160			(1,7000)		0.03
32
33												by PLU:				0.0320
34
35	105											total:				0.0320			(1,3375)		0.04
36
37												by PLU:				0.0200
38
39	106											total:				0.0200			(2,7600)		0.06
40
41												direct:				81.3000
42
43												by PLU:				2.4087
44
45	107											total:				83.7087			(1,4492)		121.31
46
47												direct:				63.0000
48
49												by PLU:				38.0000
50
51	108											total:				101.0000			(0,2171)		21.93
52
53												direct:				42.0000
54
55												by PLU:				57.0000
56
57	109											total:				99.0000			(0,5555)		54.99
58
59												direct:				19.0000
60
61												by PLU:				114.0000
62
63	110											total:				133.0000			(0,1314)		17.48
64
65												direct:				2.0000
66
67												by PLU:				6.0000
68
69	111											total:				8.0000			(0,2229)		1.78
WasteTOSHIBA

I uploaded mini-sheet of Sheet B and Sheet C.. On Sheet C there is a product D by ID 104 and doesn't have a direct: value as Product C, Product B or Product A. The given formula for product D, grabs the total value(6.86) of Product C above. So I was hopping if the product doesn't have a direct: value the formula could ignore and give 0.

 

[jasonb75]

This might work, but with such chaotic source data, anything that does work will be based on luck more than anything else.

=IFERROR(INDEX(WasteTOSHIBA!$P:$P,AGGREGATE(15,6,ROW(WasteTOSHIBA!$P$11:$A$300)/(A3,WasteTOSHIBA!$A$15:$A$304)/(WasteTOSHIBA!$L$11:$L$300="Direct:"),1)),"")

 

[cybid]

This might work, but with such chaotic source data, anything that does work will be based on luck more than anything else.

=IFERROR(INDEX(WasteTOSHIBA!$P:$P,AGGREGATE(15,6,ROW(WasteTOSHIBA!$P$11:$A$300)/(A3,WasteTOSHIBA!$A$15:$A$304)/(WasteTOSHIBA!$L$11:$L$300="Direct:"),1)),"")

This one does not work. Unfortunately it is a chaotic source data but I tried to come around the desired results.
Anyway, thank you for your time. I'll try to find some other solutions.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Vlookup, Index & Match - Grab values and compare! HELP
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[cybid]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Vlookup, Index & Match - Grab values and compare! HELP
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thnx for alerting me. And no I have cross-posted in other forums. These are only 2 forums which I'm seeking help.

 

[jasonb75]

Typo in the formula, had a comma where it should be =

=IFERROR(INDEX(WasteTOSHIBA!$P:$P,AGGREGATE(15,6,ROW(WasteTOSHIBA!$P$11:$A$300)/(A3=WasteTOSHIBA!$A$15:$A$304)/(WasteTOSHIBA!$L$11:$L$300="Direct:"),1)),"")

 

[cybid]

Typo in the formula, had a comma where it should be =

=IFERROR(INDEX(WasteTOSHIBA!$P:$P,AGGREGATE(15,6,ROW(WasteTOSHIBA!$P$11:$A$300)/(A3=WasteTOSHIBA!$A$15:$A$304)/(WasteTOSHIBA!$L$11:$L$300="Direct:"),1)),"")

Thanks to you our processing time has been great at work. Me and my team are thankful to you. Today we came across to another 'glitch', according to formula. PRDOUCT D(refering to the mini-sheet I uploaded) instead of 'by PLU:' did have only 'direct:' value which is different to PRODUCT C, because PRODUCT C has 2 values (direct: & by PLU: ). The formula did not know about this and didn't grab the 'direct:' value. We hope you could help us for the last time on this. Thank you in advance.

 

[jasonb75]

I'm not sure that I follow correctly, there is no 'direct' value for PRODUCT D, so there is no value to grab.

The way that the formula works, it looks for the product / item code in column A, then it looks for "Direct:" 4 rows above the product / item code. If it finds both then it returns the corresponding value in column P.

In your mini sheet example, the formula will return 5.67 for PRODUCT C (item 103), but will return nothing (blank) for PRODUCT D as there is no corresponding result in the data.

 

[cybid]

I'm not sure that I follow correctly, there is no 'direct' value for PRODUCT D, so there is no value to grab.

The way that the formula works, it looks for the product / item code in column A, then it looks for "Direct:" 4 rows above the product / item code. If it finds both then it returns the corresponding value in column P.

In your mini sheet example, the formula will return 5.67 for PRODUCT C (item 103), but will return nothing (blank) for PRODUCT D as there is no corresponding result in the data.

Greetings jason
Yes I can see, in that way the formula works perfectly when there is no 'direct', but when there is a product only with 'direct' value and doesn't have 'PLU" value it doesn't grab the 'direct' value.
I can post a screenshot just to show the product only with 'direct' and not 'PLU'.
I hope I described it correctly now.
Thank you