Data to match

[dinkss]

Hi there,

I have created 2 worksheets with data. First is all materials used and second has the bottle type used for each of material. How can I match it? I mean - how to assign data from bottle types to material? Is there any formula like vlookup or anything else? I have no idea how to start Can you help me please?

I just want to get YES / NO to show in the rows with material number which would indicate what bottle type is used for which product.

 

[Peter_SSs]

OK, thanks. You have entered the 8 results manually by the look of it. What about ...

and explain how you got those entered results manually?

Why does B587 get a YES? (Be specific with cell references)
Same for C587, D589 etc

 

[dinkss]

OK, thanks. You have entered the 8 results manually by the look of it. What about ...



Why does B587 get a YES? (Be specific with cell references)
Same for C587, D589 etc

I only put that manually, to show what I would like to get in each row and column.

 

[Snakehips]

I was careless with the use of $ in my untested first post.
If I understand correctly then this should hopefully give you what you are wanting.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Product	25mL Architect Natural	25mL Architect Natural Alternate	25mL Architect Black	25mL Architect Black Alternate	32mL Architect Natural	32mL Architect Natural Alternate	32mL Architect Black	32mL Architect Black Alternate	60mL Architect Natural	60mL Architect Black	4mL Round	5mL Natural	4mL Amber Glass	5mL Dropper Natural	5mL Dropper White	10ml Dropper Natural
2	Material Number	LF990933	990933	LF990935	990935	LF990937	990937	LF990939	990939	938961	G41290	LFG41132	938363	LFG41085	938731	938723	938671
3	2R98A0001
4	2R98B0001
5	2R98C0001
6	2R98D0001
7	2R98E0001
8	2R98F0001
9	2R98G0001	Yes
10	2R98G0005
11	2R98H0001		Yes	Yes
12	2R98H0005
13	2R98L0001																Yes
14	2R98M0001																Yes
15	3P25A0002
16	3P25B0002
17	3P25C0002
18	3P25D0002
19	3P25E0002
20	3P25F0002
21	3P25G0002
22	3P25G0003
23	3P25G0005
24	3P25G0006
25	3P25H0002		Yes	Yes
26	3P25H0003		Yes	Yes
27	3P25H0005
MASTER-MATERIAL

Cell Formulas
Range	Formula
B3:Q27	B3	=IF(COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3)>0,"Yes","")

 

[dinkss]

I was careless with the use of $ in my untested first post.
If I understand correctly then this should hopefully give you what you are wanting.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Product	25mL Architect Natural	25mL Architect Natural Alternate	25mL Architect Black	25mL Architect Black Alternate	32mL Architect Natural	32mL Architect Natural Alternate	32mL Architect Black	32mL Architect Black Alternate	60mL Architect Natural	60mL Architect Black	4mL Round	5mL Natural	4mL Amber Glass	5mL Dropper Natural	5mL Dropper White	10ml Dropper Natural
2	Material Number	LF990933	990933	LF990935	990935	LF990937	990937	LF990939	990939	938961	G41290	LFG41132	938363	LFG41085	938731	938723	938671
3	2R98A0001
4	2R98B0001
5	2R98C0001
6	2R98D0001
7	2R98E0001
8	2R98F0001
9	2R98G0001	Yes
10	2R98G0005
11	2R98H0001		Yes	Yes
12	2R98H0005
13	2R98L0001																Yes
14	2R98M0001																Yes
15	3P25A0002
16	3P25B0002
17	3P25C0002
18	3P25D0002
19	3P25E0002
20	3P25F0002
21	3P25G0002
22	3P25G0003
23	3P25G0005
24	3P25G0006
25	3P25H0002		Yes	Yes
26	3P25H0003		Yes	Yes
27	3P25H0005
MASTER-MATERIAL

Cell Formulas
Range	Formula
B3:Q27	B3	=IF(COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3)>0,"Yes","")

I will try it and will get back to you thanks

 

[dinkss]

I was careless with the use of $ in my untested first post.
If I understand correctly then this should hopefully give you what you are wanting.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Product	25mL Architect Natural	25mL Architect Natural Alternate	25mL Architect Black	25mL Architect Black Alternate	32mL Architect Natural	32mL Architect Natural Alternate	32mL Architect Black	32mL Architect Black Alternate	60mL Architect Natural	60mL Architect Black	4mL Round	5mL Natural	4mL Amber Glass	5mL Dropper Natural	5mL Dropper White	10ml Dropper Natural
2	Material Number	LF990933	990933	LF990935	990935	LF990937	990937	LF990939	990939	938961	G41290	LFG41132	938363	LFG41085	938731	938723	938671
3	2R98A0001
4	2R98B0001
5	2R98C0001
6	2R98D0001
7	2R98E0001
8	2R98F0001
9	2R98G0001	Yes
10	2R98G0005
11	2R98H0001		Yes	Yes
12	2R98H0005
13	2R98L0001																Yes
14	2R98M0001																Yes
15	3P25A0002
16	3P25B0002
17	3P25C0002
18	3P25D0002
19	3P25E0002
20	3P25F0002
21	3P25G0002
22	3P25G0003
23	3P25G0005
24	3P25G0006
25	3P25H0002		Yes	Yes
26	3P25H0003		Yes	Yes
27	3P25H0005
MASTER-MATERIAL

Cell Formulas
Range	Formula
B3:Q27	B3	=IF(COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3)>0,"Yes","")

I am super delighted! It is working 100% Thank you so much!

Can you please explain to me the formula you used? So I can understand this? It would be very helpful for me as I am starting my adventure with Excel.

Thanks in advance!

 

[Snakehips]

@dinkss Great that we were able to get you up and running.

Essentially, the solution is checking each Product & Bottle Type column to see if the Material listed in Master-Material A exists.
This could be done in various ways. I chose to use the COUNTIF function. @Peter_SSs chose to use the MATCH function.

The whole thing is made fairly simple because your Product headings in both sheets are in the same order. The only difference is that on Product & BT they run from column A: ... and on the Master-Material they run from column B: .... So to check the product in Master B, we check column A of P&BT

The material we are checking is always in Master A so make that column absolute, using $A Leave the row number as relative, so that it increments as and when formula pulled down.
Thus first material reference is $A3

COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3) - Asks how many instances of Material $A3 in the P&BT range A$3:A$200
If material $A3 is listed one or more times the COUNTIF will return the count as a positive integer. If not listed, the COUNTIF will return zero, 0.

=IF(COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3)>0,"Yes","")
The IF statement simple says return 'Yes' if the COUNTIF is greater than zero, otherwise return an empty cell.

As the formula is dragged down, $A3 increments to compare the material of each row.
As the formula is dragged across, the Countif range A$3:A$2000 increments to become B$3:B$200.... etc.

Hope that helps.

 

[dinkss]

@dinkss Great that we were able to get you up and running.

Essentially, the solution is checking each Product & Bottle Type column to see if the Material listed in Master-Material A exists.
This could be done in various ways. I chose to use the COUNTIF function. @Peter_SSs chose to use the MATCH function.

The whole thing is made fairly simple because your Product headings in both sheets are in the same order. The only difference is that on Product & BT they run from column A: ... and on the Master-Material they run from column B: .... So to check the product in Master B, we check column A of P&BT

The material we are checking is always in Master A so make that column absolute, using $A Leave the row number as relative, so that it increments as and when formula pulled down.
Thus first material reference is $A3

COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3) - Asks how many instances of Material $A3 in the P&BT range A$3:A$200
If material $A3 is listed one or more times the COUNTIF will return the count as a positive integer. If not listed, the COUNTIF will return zero, 0.

=IF(COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3)>0,"Yes","")
The IF statement simple says return 'Yes' if the COUNTIF is greater than zero, otherwise return an empty cell.

As the formula is dragged down, $A3 increments to compare the material of each row.
As the formula is dragged across, the Countif range A$3:A$2000 increments to become B$3:B$200.... etc.

Hope that helps.

OH WOW, thanks for great explanation,
This is exactly what I needed

Thanks so much for all the help! I really appreciate it.

 

[dinkss]

@dinkss Great that we were able to get you up and running.

Essentially, the solution is checking each Product & Bottle Type column to see if the Material listed in Master-Material A exists.
This could be done in various ways. I chose to use the COUNTIF function. @Peter_SSs chose to use the MATCH function.

The whole thing is made fairly simple because your Product headings in both sheets are in the same order. The only difference is that on Product & BT they run from column A: ... and on the Master-Material they run from column B: .... So to check the product in Master B, we check column A of P&BT

The material we are checking is always in Master A so make that column absolute, using $A Leave the row number as relative, so that it increments as and when formula pulled down.
Thus first material reference is $A3

COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3) - Asks how many instances of Material $A3 in the P&BT range A$3:A$200
If material $A3 is listed one or more times the COUNTIF will return the count as a positive integer. If not listed, the COUNTIF will return zero, 0.

=IF(COUNTIF('PRODUCT & BOTTLE TYPE'!A$3:A$2000,$A3)>0,"Yes","")
The IF statement simple says return 'Yes' if the COUNTIF is greater than zero, otherwise return an empty cell.

As the formula is dragged down, $A3 increments to compare the material of each row.
As the formula is dragged across, the Countif range A$3:A$2000 increments to become B$3:B$200.... etc.

Hope that helps.

Just a question again - is VLOOKUP function better to use? If yes is it possible to use it in my case? How could I do that?

 

[dinkss]

I cannot see how you got that particular "Yes" and "No" in your images, but my best guess is for cell B2 in 'MASTER-MATERIAL', and copied across and down after adjusting ranges if required:

=IF(ISNUMBER(MATCH($A2,FILTER('PRODUCT & BOTTLE TYPE'!$C$3:$Z$100,'PRODUCT & BOTTLE TYPE'!$C$2:$Z$2=B$1,""),0)),"Yes","No")

Just a question again - is VLOOKUP function better to use? If yes is it possible to use it in my case? How could I do that?

 

[Snakehips]

Just a question again - is VLOOKUP function better to use? If yes is it possible to use it in my case? How could I do that?

No. You are merely checking for the existence within a series if single column lists. Countif and Match are the goto functions.