Compare Two Pairs of Cells and Mark Rows with Matches

[bzowk]

Hey Guys -

I have two worksheets which each have about 6 columns. Examples below...

Worksheet #1 Example (Original)
A B C D E F G
PCModel1 PCManufacturer1 Value Storage CPU Link1 Link2
PCModel2 PCManufacturer1 Value Storage CPU Link1 Link2
PCModel3 PCManufacturer2 Value Storage CPU Link1 Link2
PCModel4 PCManufacturer1 Value Storage CPU Link1 Link2
...

Worksheet #2 Example (Original)
A B C D E
PCModel2 PCManufacturer2 2gbRAM Storage CPU
PCModel3 PCManufacturer1 4gbRAM Storage CPU
PCModel6 PCManufacturer2 2gbRAM Storage CPU
PCModel8 PCManufacturer1 4gbRAM Storage CPU
...

What I'm needing overall is Link2 from G on Worksheet #1 for each matching PCModel & Manufacturer (Columns A+B on Worksheet #2 ) which has 2gb of RAM.

To start, I've already removed all rows from WS#2 which were not 2gb of RAM to produce the below:

Worksheet #2 Example (1st Modification)
A B C D E
PCModel2 PCManufacturer2 2gbRAM Storage CPU
PCModel6 PCManufacturer2 2gbRAM Storage CPU
...

I then copied columns A+B from WS#2 into columns H+I in WS#1 so that they are all in a single worksheet like the below:

New Combined Worksheet Example
A B C D E F G H I
1 PCModel1 PCManufacturer1 Value Storage CPU Link1 Link2 PCModel2 PCManufacturer1
2 PCModel2 PCManufacturer1 Value Storage CPU Link1 Link2 PCModel6 PCManufacturer2
3 PCModel3 PCManufacturer2 Value Storage CPU Link1 Link2 PCModel5 PCManufacturer1
4 PCModel4 PCManufacturer1 Value Storage CPU Link1 Link2 PCModel2 PCManufacturer1

Now I'm just needing to have Excel do the below which is where I'm stuck...
- Search columns A+B for a match of H1+I1
- If match found, add the text "Match" into a new column (J) on the matching row
- Repeat last two steps for H2+I2 and so on until complete

Example of Desired Result
A B C D E F G H I J
1 PCModel1 PCManufacturer1 Value Storage CPU Link1 Link2 PCModel2 PCManufacturer1
2 PCModel2 PCManufacturer1 Value Storage CPU Link1 Link2 PCModel6 PCManufacturer2 Match
3 PCModel3 PCManufacturer2 Value Storage CPU Link1 Link2 PCModel5 PCManufacturer1
4 PCModel5 PCManufacturer1 Value Storage CPU Link1 Link2 PCModel2 PCManufacturer1 Match

If I can get it to do that, I'll be able to filter the list to get the links I need for rows with "Match" in column J

An suggestions? Thanks!

 

[jtakw]

Hi,

This does what you described; however, your sample results does Not seem correct, unless I'm misunderstanding something:

Book1
	A	B	C	D	E	F	G	H	I	J
1	PCModel1	PCManufacturer1						PCModel2	PCManufacturer1	Match
2	PCModel2	PCManufacturer1						PCModel6	PCManufacturer2
3	PCModel3	PCManufacturer2						PCModel5	PCManufacturer1	Match
4	PCModel5	PCManufacturer1						PCModel2	PCManufacturer1	Match
Sheet433

Cell Formulas
Range	Formula
J1	=IF(COUNTIFS(A$1:A$4,H1,B$1:B$4,I1),"Match","")

Formula copied down.