asked on

excel copy contents from one worksheet column to another worksheet

I have an xls with two tabs Report & wBPS

Both tabs have a common column called Opportunity Identifier

in tab Reports I have an empty column called BPS that I would like to be a lookup to grap the contents of the column called BPS from wBPS when Opportunity Identifier is the same in both.

thanks

sentner

Sounds like you just want a vlookup. Can't give you the exact syntax to use without more details about what is in which column, but it's basically:

VLookup( value, table_array, index_number, not_exact_match )

So, if the wBPS tab had the ID in column A, and the BPS in column C, and the Report column A was the ID as well, you'd set the formula in the Report BPS cell B2 to:

=vlookup(A2,wBPS!A:C, 3, FALSE)

See: http://www.techonthenet.com/excel/formulas/vlookup.php

Matt Pinkston

ASKER

Column A in both is the Optortunity ID and Column B in wBPS is the status I want to vcopy

tried =VLOOKUP(A2,wBPS!A:B, 3, FALSE)

weird results everything was either #NA or #REF

Elton Pascua

Here's a sample file on how VLOOKUP as suggested by sentner is used. I also provided an alternate method which is INDEX + MATCH. The benefit of the latter is if your lookup column is not the first column.
vlookup-index-match.xlsx

Elton Pascua

Can you upload a sample file? I'm guessing the format of the data in those columns is not the same (e.g. one is formatted as text, the other as numbers).

SOLUTION

sentner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Matt Pinkston

ASKER

senter, that worked
=VLOOKUP(A2,wBPS!A:B, 2, FALSE)

But how do you get rid of the #NA if there was no hit on the lookup...

Also what would a return of 0 mean?

ASKER CERTIFIED SOLUTION

Elton Pascua

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial