beginner question

[shdwfx]

Sorry all but I'm a newbie so apologies if the answer is in here somewhere already because I probably might not recognize it. I have a list of material numbers in spreadsheet A and a list of materials with additional related data elements in spreadsheet B. I need a formula to take a material from A, look for a match in B. If there is a match, also look for value 'x'. If both conditions match, give me a result of Yes. I hope I have explained that clearly enough and any help at all is greatly appreciated!

 

[bryonwoods30]

does sheet b also have the matieral number? and is the value x that you are going to be looking for always appear in the same column in sheet b?

 

[shdwfx]

Yes and yes. Thanks!

 

[bryonwoods30]

in your sheet b, you need to highlight all columns and make sure the data is sorted ascending by the material number.
in sheet a, you will need to additional columns for the formulas, and one for your final result.
so in sheet a you will have the columns
material number,value,material number match,value match

 

[shdwfx]

yep I know about the sorting in ascending on the sheet I'm looking up on but why do I need separate columns for material match and value match - I'm doing all this in one formula with the result of that formula in one column. Maybe I'm not explaining correctly.

Spreadsheet A example:
A B C
Material# Exclusion for Canada Exclusion for Mexico
1 (is there an excl?)
2

Spreadsheet B:
Material # Exclusion
1 MX
2 CA
2 MX

I am trying to figure out the formula to enter in cell B2 to look for a match on material number in spreadsheet B. If it finds a match, does the material have an exclusion for Canada. If so, put a Y or Yes in cell B2. Then of course copy it down and over to check for Mexico exclusions.

 

[shdwfx]

*sigh* sorry, I typed up the example data but it didn't stay the way I entered it.

 

[bryonwoods30]

=vlookup(lookup value,table on sheetb to look in,column to return from table)

so in your case on sheet A in cell b1

=vlookup(a1,highlight the table in sheet b with the material number as your first column you highlight,the column number you are returning material number is column 1 so your exclusion is column 2 in your example)

 

[snoopyhr]

Here is my 2 cents on this,

Excel Workbook
	A	B	C	D	E
6	Material No.	Value X - Entered manually	Result
7	MAT-2944	40	Yes	Value X and Mat. No Match to Master
8	MAT-2133	50	No	Data not in Sync
9	MAT-1867	20	Yes	Value X and Mat. No Match to Master
10	MAT-2612	25	No	Data not in Sync
11	MAT-1960	22	No	Data not in Sync
12	MAT-2495	47	Yes	Value X and Mat. No Match to Master
13	MAT-1732	28	Yes	Value X and Mat. No Match to Master
14
15	Sheet2 = your Sheet B
Sheet3