Using XLookup or related formulas to look up information on one sheet and put in a column on another

[ABirdLibrary]

I have lists of students alphabetically by grade level. From another system, I get lists of all students, past and present, alphabetically by their library #. I need to be able to take the barcode from my second sheet with the barcodes and put in the first sheet of my students.  

I have a terrible time trying to do this.  I have tried xlookup, nested x lookup, vlookup, index and match.  I feel like this should be simple, however I can't get this to work.  I arrange things with headers like Name, ID Number, library #, Grade, etc.  

I need to be able to look up every student by name and find the matching library #.  

How can I best accomplish this? I use excel on mac. 

[#2]

@ABirdLibrary Do you get errors or do the formulae that you tried not return matching records? Could be that the lookup values in the current student listing are not exactly the same as in the list from the other system (e.g. leading or trailing spaces) or it could be a formatting issue (number vs. text).

Without an example you both lists, it's difficult to know what's causing the problem. Be ware no to share any private and confidential information (like names and addresses of real people) in case you choose to upload files here.

[#3]

Here is an example.  I just usually get NA.  I have tried using xlookup nested within, vlookup, etc. But it never seems to work.  @Riny_van_Eekelen 

[#4]

@ABirdLibrary The problem is what you are feeding the XLOOKUP() function.  Here is the formula you want in the example:

=XLOOKUP(C2:C6,'student library #'!C:C,'student library #'!D:D)

The "lookup_value" is the value you are using to match the corresponding row in the other table.  In this case it is Student Name.  I used C2:C6 to create a dynamic array that will automatically fill down for all those rows but you could use C2 and then copy or fill down to insert the corresponding formula in each row.

I hope that helps.

[#5]

Thank you so much! That really did help,@mtarler