Vlookup

[Sky]

, IHi Everyone,

I had two big file. In the file all begin with same P/N but had some
different information.
I can use Vlook to transfer column by column but if I had more than 10k P/N
and more than 50 over information, I need to repeat again and again.
Cannot use cut and paste because the file 1 are the master list with 10k P/N
but file 2 only had 2k P/N so I need to Vlookup the file

The below show some example
File 1
P/N Orgin Cost PO Qty
12345 USA $2 1000 10

File 2
P/N Orgin Date Recieve Description
12345 USA 10 Jul 12 Jul ABC

I wish to have the below information
P/N Orgin Cost PO Qty Date Recieve Description
12345 USA $2 1000 10 10 Jul 12 Jul ABC

 

[Jacob Skaria]

Let Sheet1 be as below
P/N Orgin Cost PO Qty
12345 USA $2 1000 10

and Sheet2 as below
P/N Orgin Date Recieve Description
12345 USA 10 Jul 12 Jul ABC

Sheet3 would be copy of Sheet1....Add the headings ( Date Recieve
Description)
P/N Orgin Cost PO Qty Date Recieve Description
12345 USA $2 1000 10 =For1 =For2 =For3

In F2 the formula would be
=VLOOKUP(A2,Sheet2!$A$2:$E$10000,3,0)

In G2
=VLOOKUP(A2,Sheet2!$A$2:$E$10000,4,0)

In H2
=VLOOKUP(A2,Sheet2!$A$2:$E$10000,5,0)

and copy the formulas down as required....


If this post helps click Yes

 

[Sky]

Hi Jacob,

Yes, column by column can get the ans but I had 50 to match and need to do
50 time.
Is there any way one time can get all 3 column? Just one formula to get 3
column.

 

[RagDyeR]

When you say "50 to match", do you mean 50 columns?

If so, use the Columns() function to *automatically* increment the columns
index number as you copy across.

For example:
=Columns($A:C) = 3

The actual column ID of the data columns is *irrelevant*.

=Vlookup($A1,$G$5:$L$100,4,0)
Can be written as:
=Vlookup($A1,$G$5:$L$100,Columns($A),0)

As you copy the above formula across, the column index increments
automatically.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi Jacob,

Yes, column by column can get the ans but I had 50 to match and need to do
50 time.
Is there any way one time can get all 3 column? Just one formula to get 3
column.