Forum Discussion
Searching for information in one spreadsheet and returning it another
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
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
- Your formula does not work for more than one name. I need to look for and copy a large series of job titles (around 5,000) from one spreadsheet to another, so I think I still need to use XLOOKUP (or VLOOKUP). So please have another think about this.
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.- Thanks James. I took your advice and changed 'name' to 'employee code' and it worked. Have a Merry Christmas! Jonathan
