Reference Table - VLookUp

[MR3]

if i have a table with values and I am comparing it to my export data from M$ Access in Excel, where Sheet1 is my data and Sheet2 is my Table of values can i use VLookUp to compare and change interior colors if the values do not match up?

 

[Robert Mika]

You can use VLOOKUP to matach data/values and then use CF to highlight the one which do not match up.

Can you post an example?

 

[MR3]

Hey Robert,

here is my example for Honda Car Parts



Sheet3 is my data table that i am trying to match up data on Sheet4 which is the master table of values of what the parts should be. So if they dont match up, my idea was to change the interior color of the cells that don't match up on Sheet3

 

[Robert Mika]

Do you want to match thepart only o the part and cost?
Are the TRUE/FALSE statment a part of your match?
Asuming that the result will be column D sheet 3 and you match Column A in Sheet 3 with row 1 sheet 4 the answers will be all TRUE or "MATCH"

 

[MR3]

Everything in the Sheet4 Table is part of the match

 

[Robert Mika]

Try this:
D2 Sheet 3
=IF(ISNUMBER(MATCH(A2&B2&C2,Sheet4!$B$1:$P$1&Sheet4!$B$2:$P$2&Sheet4!$B$3:$P$3,0)),"match","no match")

(array entered confirm CTRL+SHIFT+ENTER)

copy down

 

[MR3]

hey Robert,
i tried your line of code and it says no match for any of the 3 rows of the data that i am concerned with. I am not sure why, i think at least one row should match.

 

[markmzz]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Honda Part #</td><td style="font-weight: bold;text-align: center;;">Cost</td><td style="font-weight: bold;text-align: center;;">Replaceable</td><td style="font-weight: bold;text-align: center;;">Match</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Section Parts</td><td style="text-align: center;;">90665-S04-003</td><td style="text-align: center;;">19200-P0A-003</td><td style="text-align: center;;">77253-S04-000</td><td style="text-align: center;;">72577-A04-001</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">77253-S04-000</td><td style="text-align: center;;"></td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;">No</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Cost</td><td style="text-align: center;;">5</td><td style="text-align: center;;">10</td><td style="text-align: center;;">3</td><td style="text-align: center;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">90665-S04-003</td><td style="text-align: center;;">6</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">No</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Replaceable</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">19200-P0A-003</td><td style="text-align: center;;">10</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">No</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #C5D9F1;;">72577-A04-001</td><td style="text-align: center;background-color: #C5D9F1;;">50</td><td style="text-align: center;background-color: #C5D9F1;;">TRUE</td><td style="text-align: center;background-color: #C5D9F1;;">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">Sheet3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">****</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">Sheet4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--(<font color="Green">A2=G$1:J$1</font>),--(<font color="Green">B2=G$2:J$2</font>),--(<font color="Green">C2=G$3:J$3</font>)</font>),"Yes","No"</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[MR3]

Does it matter if I am using Excel 2003?

 

[Robert Mika]

No, it shoudl not.
From example you have provided none is a match unless column C and row 3 is not taking into calcualtion.