Copy cell data from one worksheet to another worksheet only if both worksheets unique value matches

Super User Asked by imshadow on January 1, 2022

I’m new to excel & i’m hating so much but it’s uses are vast. Now for my query

  • I’ve a workbook containing three sheets ‘Project A’ , ‘Project B’ & ‘Project C’
  • Project A & Project B as same unique values but different descriptions
  • Now using project A & Project B unique values the different descriptions should be display in seperate columns with its respective unique value project C. so total 3 columns in Project C. This is my query?
  • Can anyone point me out what formula to use or VBA or filter ? Help out this please.

One Answer

This can be done easily with XLOOKUP (Or with VLOOKUP if you're using an older version that doesn't support XLOOKUP).

On Project C, you should use the following in the column with the description you get from Project A:

=XLOOKUP($A2,'Project A'!$A:$A,'Project A'!$B:$B,"Not found")

$A2 is the reference for the unique value (Preferably within the same row of this cell);
'Project A'!$A:$A is the range of the unique values' column on Project A sheet;
'Project A'!$B:$B is the range of the descriptions' column on Project A sheet;
"Not found" is the text that will appear if the unique value is not found on Project A. This is an optional field - you can leave it empty, so if the value won't be found a #N/A error will be the result of this function instead.

Another neater way of using the same function, is with named ranges - using Tables. Define the data tables on each of your sheets as Excel Tables by selecting them and pressing ctrl+T or Insert -> Table.

Now, you can rename each table (let's say - tblProjectA is the name of the table in Project A) and use its name and its columns' header name in the function (let's say that the "unique value" column header is "ID", and "Description" is the header of the description column in tblProjectA):

=XLOOKUP([@ID],tblProjectA[ID],tblProjectA[Description],"Not found")

Here's an example:

These are 3 sheets for example -

enter image description here enter image description here enter image description here

While this is the actual formulas written in Project C sheet:

enter image description here

Answered by shemadolev on January 1, 2022

Add your own answers!

Related Questions

Creating a symbolic link to mapped network drive in Windows

8  Asked on December 31, 2020 by vegard-larsen


Bash files open terminal when opened

1  Asked on December 31, 2020 by chris-smith


S3 command not working in cron

1  Asked on December 31, 2020 by kargirwar


Scale to fit option

6  Asked on December 30, 2020 by steven-penny


After downloading Tweepy I don’t know where to locate it

1  Asked on December 30, 2020 by candle


Excel COUNTIF with non-contiguous named ranges

1  Asked on December 30, 2020 by malan88


How to bypass Windows 10 PIN complexity requirement

1  Asked on December 29, 2020 by some-user


Resume uploading with built-in Windows FTP client

1  Asked on December 29, 2020 by vlad-novakovsky


What is the Windows analog of the Linux watch command?

15  Asked on December 29, 2020 by peterjclaw


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP