Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Help copying the name assigned a phone number name anywhere in a spreadsheet to the next occurrance?

Iron Contributor

Hello,

 

Every month I take all the phone calls I've received - some have the name of the phone number next to them - some don't and add it as a new sheet to a spreadsheet. The workbook has two years worth of phone numbers and names.

 

Every month - phone number by phone number I do a find for that phone number in the spreadsheet and copy the name that I've assigned next to it in the past to the new occurrance for the current month. I'd love to create a formula that does this for me.

 

For example in the new sheet if the value of A4 is 818-555-1212 search through the workbook for the previous occurrence of that value that doesn't have a blank in the immediate column to the right of it and assign that value to the cell B4 in the latest spreadsheet. So if somewhere else in the workbook on a previous sheet it sees in A27 that 818-555-1212 has a B27 of Jane Doe it then copies that Jane Doe to B4 on the new spreadsheet.

 

I could add a blank column to the new spreadsheet so I don't overwrite any cells with the name already filled in.

3 Replies

@Boe Dillard 

Insert an empty column in column C,

In C2:

=XLOOKUP(A2,A$2:A2,B$2:B2,"")&""

Fill down as far as you want.

Thanks - maybe I'm not explaining well.

I have a workbook with multiple pages. The first sheet of the workbook has A1 through B1000 filled in:
Sheet 1 (Phone#)
A1 818-555-1212 B1 Jane Doe
A2 818-555-1214 B2 John Doe
A3 818-555-1215 B3 Mary Smith
A4 818-555-1218 B4 Bob Smith

Sheet2 might be (NewCalls)
A1 818-555-1218 B1 blank/no value
A2 818-555-1214 B2 blank/no value

I want to have the second sheet reference the values in the first sheet to fill in for B1, B2...

 

I think it would be something on the second page  in column C =XLOOKUP(A1,'Phone#'!A$1:A$1000,'Phone#'!B$1:B$1000)  

Unfortunately that gives me the phone number instead of the name so I'm off somewhere.

Got it - solved =XLOOKUP(B4,'Phone#'!B$1:B$1000,'Phone#'!A$1:A$1000)

Thanks