Dec 22 2022 08:49 AM
I am trying to get information in one spreadsheet to appear in another using VLOOKUP and XLOOKUP. Can you help?
Dec 22 2022 08:57 AM
Let's say you have a value in A2 on the current worksheet.
You want to look up this value in D2:D100 on a sheet named Data Sheet, and return the corresponding value from F2:F100 on that sheet.
=XLOOKUP(A2, 'Data Sheet'!$D$2:$D$100, 'Data Sheet'!$F$2:$F$100, "")
or
=IFERROR(VLOOKUP(A2, 'Data Sheet'!$D$2:$F$100, 3, FALSE), "")
The formula can be filled down if required.
Dec 22 2022 09:29 AM
Hi Hans
I'm still struggling and can't get the formula you suggest to work. The information for each person can only be found on one line, not a range as you suggest, so can you tell me what is wrong with the following formula:
=XLOOKUP(F5,'All People'!$L$4,'All People'!$P$4)
F5 has the name of the person in the first sheet, L4 is where the same name is to to be found in the second sheet called All People, and P4 is the job title of the same person in the second sheet. I want to copy that job title to the first sheet.
Thanks
Jonathan
Dec 22 2022 11:35 AM
If you already know that the name is in L4, you don't need a lookup formula. You can simply use
='All People'!$P$4
Dec 23 2022 01:09 AM
Dec 23 2022 03:02 AM
assuming your source information table is laid out the way I think it is, the following should work for you:
=XLOOKUP(F5,'All People'!$L:$L,'All People'!$P:$P)
XLOOKUP and VLOOKUP take a value, match it to a corresponding value in a defined range, and return the matching information from another. F5 needs to be your Name (ideally unique, so an employee code might be better), ‘All People’!$L:$L needs to be the search range (I.e. where the names are kept) and ‘All People’!$P:$P should be where the information you want to return is. In your example you referenced a specific cell for the last two, rather than the range where the information could be found.
Dec 23 2022 04:11 AM