Excel Copy Column Value Based On Another Column Value Lookup

Problem:
One Excel sheet named “Account Info” contains accountnumber and account name. The other sheet named “Order Info” contains order mapping between orderid and accountnumber. Now we need to add another column to the second sheet to show the matching customer name from the first sheet. The two sheets are shown below.

account

order

Solution:
The build in functions IFERROR and VLOOKUP can be used together for this job. You can simply enter

=IFERROR(VLOOKUP(B2, ‘Account Info’!A:B, 2, 0),”N/A”)

as the formula of the cell C2 on the sheet “Order Info”. Then drag it all the way down to cover all your rows.

Explanation:
VLOOKUP can be used to look up a specified value (its first parameter in this example) from the specified table data(the 2nd parameter) and return the matched data(the third parameter). 0 here indicates exact match. If no match can be found, then IFERROR will set the value to “N/A”. See Microsoft Support for more details.

Advertisements
This entry was posted in Excel and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s