If Cell 1 in Sheet 1, Matches Cell 1 in Sheet 2, then copy and paste Cell 2, 3, & 4 to Sheet 2 Cell 2, 3 & 4

PlusBob

New Member
Joined
Feb 16, 2016
Messages
13
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Without using VBA (government PCs with it's security), I want to link a if another cell matches the criteria then copy and paste it.

I have an employee tracker on a workbook. One with sheet 1 (Current), it's all of the info that I need to include where are they sitting (location and cube#). Needless to say it has personal info on it. On another sheet 2 in the workbook (Location 1), I have an image with columns. The image is a floor plan with quick location numbers (1-108), the columns are: Quick Location# - Location 1# (these are the actual cube#) - Employee Name - Phone (desk) - Remarks. This sheet gets printed to PDF for the all to see.

I want the formula to read sheet 1 to find on each row that matches the Location 1# cell in sheet 2, and then populate the Employee Name and Phone column from sheet 1 to sheet 2.

(I'm sorry that I'm not explaining this correctly, asking for something already asked for (I did look), or asking for magic to happen know that this is a spreadsheet not a sorcerer's cauldron.)

Thank you for your insights and wisdom and your time spent on this.


Sheet 1: CurrentSheet 2: Location 1
EmployeesLocationCubeDesk PhoneQuick Loc#CubeEmployeePhone
Adams, John1A200303-555-12121A200Adams, John303-555-1212
Adams, John Q2B100303-555-12132
A201
Arthur, Chester
303-555-1214
Arthur, Chester1A201303-555-12143A202Hamilton, Alex303-555-1217
Bob, Plus1Closet303-555-12154A203
Franklin, Ben2B101303-555-12165A204
Hamilton, Alex1A202303-555-12176A205
Lincoln, Abe2B102303-555-12187ClosetPlus, Bob303-555-1215
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,812
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCD
1EmployeesLocationCubeDesk Phone
2Adams, John1A200303-555-1212
3Adams, John Q2B100303-555-1213
4Arthur, Chester1A201303-555-1214
5Bob, Plus1Closet303-555-1215
6Franklin, Ben2B101303-555-1216
7Hamilton, Alex1A202303-555-1217
8Lincoln, Abe2B102303-555-1218
9
Sheet1


+Fluff 1.xlsm
ABCD
1Quick Loc#CubeEmployeePhone
21A200Adams, John303-555-1212
32A201Arthur, Chester303-555-1214
43A202Hamilton, Alex303-555-1217
54A203  
65A204  
76A205  
87ClosetBob, Plus303-555-1215
Sheet2
Cell Formulas
RangeFormula
C2:C8C2=IFNA(INDEX(Sheet1!$A$2:$A$8,MATCH(B2,Sheet1!$C$2:$C$8,0)),"")
D2:D8D2=IFNA(INDEX(Sheet1!$D$2:$D$8,MATCH(B2,Sheet1!$C$2:$C$8,0)),"")
 
Solution

PlusBob

New Member
Joined
Feb 16, 2016
Messages
13
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
How about
+Fluff 1.xlsm
ABCD
1EmployeesLocationCubeDesk Phone
2Adams, John1A200303-555-1212
3Adams, John Q2B100303-555-1213
4Arthur, Chester1A201303-555-1214
5Bob, Plus1Closet303-555-1215
6Franklin, Ben2B101303-555-1216
7Hamilton, Alex1A202303-555-1217
8Lincoln, Abe2B102303-555-1218
9
Sheet1


+Fluff 1.xlsm
ABCD
1Quick Loc#CubeEmployeePhone
21A200Adams, John303-555-1212
32A201Arthur, Chester303-555-1214
43A202Hamilton, Alex303-555-1217
54A203  
65A204  
76A205  
87ClosetBob, Plus303-555-1215
Sheet2
Cell Formulas
RangeFormula
C2:C8C2=IFNA(INDEX(Sheet1!$A$2:$A$8,MATCH(B2,Sheet1!$C$2:$C$8,0)),"")
D2:D8D2=IFNA(INDEX(Sheet1!$D$2:$D$8,MATCH(B2,Sheet1!$C$2:$C$8,0)),"")

You the Fluff! :) Thank you--that is exactly what I needed! And, educational, because I had never knew what to use IFNA statement for. Thank you very much for your time on this--really appreciate it!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,812
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,080
Messages
5,639,947
Members
417,120
Latest member
Pavithra devi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top