Compare two columns from one sheet to two columns in other sheet and fill third column

Dear All,

This is my first post here.

I have two sheets and I want to compare values in two columns of sheet1 to values in two columns of sheet 2. When ever both the values match, I want to write corresponding third column value from sheet 1 to sheet 2.

Here I go ..

Sheet 1:
Product Customer Market
Type A HUTCHINSON parts
Type B NP PODOLSKKABEL OAO auto
Type V TISSAN OOO fuel line
XYZ INTELS OOO pipes


Sheet 2:
Product Customer Market
Type A HUTCHINSON
Type B NP PODOLSKKABEL OAO
Type Z TISSAN OOO
Product A INTELS OOO
Type B ABC
Type V TISSAN OOO
XYZ INTELS OOO


I want to fill third column in sheet 2 after comparing values with sheet 1 ...

I want to get something like following in the "market" column ..

Product Customer Market Expected result
Type A HUTCHINSON parts
Type B NP PODOLSKKABEL OAO auto
Type Z TISSAN OOO
Product A INTELS OOO
Type B ABC
Type V TISSAN OOO fuel line
XYZ INTELS OOO pipes

Please can you help me with any formulae or something that helps me to get expected result?

Thanks,
Prashant

Experiment.xls

I am not sure if the following is the best solution for your problem. You can use it till any one else provide better solution. :-)

Step1:

Copy formula =VLOOKUP(A2,sheet1!A:A,1,0) in cell E2 and drag downwards

Step2:

Copy formula =VLOOKUP(B2,sheet1!B:B,1,0) in cell F2 and drag downwards

Step3:

Copy formula =IF(AND(E2<>"",F2<>""),VLOOKUP(E2,sheet1!A:C,3,0),"") in cell C2 and drag downwards

Hope this helps you.


---------------------------------------------------------------------------------------------------------------------------------------------------------
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below.

Thanks reddy. This helps a lot.

You are welcome Prashant.


If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below.

Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

Reddy,

There is a problem with the solution. When it matches the first value and does not match the second value then it produces the wrong result. Please see attached excel (ver02) file.

Thanks,
Prashant