Excel Lookup values from two lists

[VBAmature31]

Hi all,

I have been struggling with something that I hope you guys can help me out with,

I have two lists on two sheets in the same workbook.


sorry I'm finding this hard to explain,I have attached a workbook with 2 sheets and highlighted on both sheets what I need to validate.

I need to validate everything in sheet1 one Column B,E &N match sheet2 column C,F, & J.

Highlight the differences on both.

Any help here would be much appreciated.

Thanks
KD

 

[Deepak]

Is this something what you are looking for!

 

[VBAmature31]

Is this something what you are looking for!

Hi Deepak,

Yes this will work perfectly for what I want,thank you for your quick reply and help,

Its much appreciated!!

Thanks
KD

 

[VBAmature31]

Is this something what you are looking for!

Hi Deepak,

Sorry to bother you but one quick question,I can't seem to change the range past row 35,when I extend the formula past that range is gives errors(i.e. =MATCH(B18&E18&N18,'S-O Upload'!$C$2:$C$50&'S-O Upload'!$F$2:$F$50&'S-O Upload'!$J$2:$J$50,0)?

It only works with below:

=MATCH(B18&E18&N18,'S-O Upload'!$C$2:$C$35&'S-O Upload'!$F$2:$F$35&'S-O Upload'!$J$2:$J$35,0)

Do you know why this is?

Thanks
KD

 

[bosco_yip]

For the original formula range 35 change to 50, kindly be noted :

This is an array formula, confirm pressing with SHIFT+CTRL+ENTER 3 keys together in enter.

=MATCH(B2&E2&N2,'S-O Upload'!$C$2:$C$50&'S-O Upload'!$F$2:$F$50&'S-O Upload'!$J$2:$J$50,0)

Or......

You can try ths non-array formula instead :

=INDEX(MATCH(B2&E2&N2,'S-O Upload'!$C$2:$C$50&'S-O Upload'!$F$2:$F$50&'S-O Upload'!$J$2:$J$50,0),0)

Regards
Bosco

 

[Deepak]

VBAmature31
bosco_yip Just hit the hammer on right point.

 

[VBAmature31]

For the original formula range 35 change to 50, kindly be noted :

This is an array formula, confirm pressing with SHIFT+CTRL+ENTER 3 keys together in enter.

=MATCH(B2&E2&N2,'S-O Upload'!$C$2:$C$50&'S-O Upload'!$F$2:$F$50&'S-O Upload'!$J$2:$J$50,0)

Or......

You can try ths non-array formula instead :

=INDEX(MATCH(B2&E2&N2,'S-O Upload'!$C$2:$C$50&'S-O Upload'!$F$2:$F$50&'S-O Upload'!$J$2:$J$50,0),0)

Regards
Bosco

Hi Bosco,

Thank you worked a treat,

sorry feel like such a dummy,

appreciate the help.

Thanks
KD