Searching for information in one spreadsheet and returning it another

[JonathanHolt]

I am trying to get information in one spreadsheet to  appear in  another using VLOOKUP and XLOOKUP. Can you help?

[#2]

@JonathanHolt 

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.

[#3]

@Hans Vogelaar 

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

[#4]

@JonathanHolt 

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

[#5]

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.

[#6]

@JonathanHolt 

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.

[#7]

Thanks James. I took your advice and changed 'name' to 'employee code' and it worked. Have a Merry Christmas! Jonathan