How to use XLOOKUP between two spreadsheets correctly?

Question

How to use XLOOKUP between two spreadsheets correctly?

Anonymous

Hello!

I have two spreadsheets and I am trying to pull data from one spreadsheet to another using the XLOOKUP function. I am trying to pull over a value if a row has a matching ID and Term as the other spreadsheet.

For example I have IDs in both sheets, I have Terms in both sheets, but only one of the spreadsheets has the Value. I need to get the value to the other spreadsheet if it matches the ID and Term.

Spreadsheet 1 ex.

ID	TERM
1111	Fall
2222	Winter
3333	Summer

Spreadsheet 2 ex.

ID	TERM	VALUE
1111	Fall	3.5
2222	Winter	4.0
2222	Fall	2.2
4444	Summer	3.6

So using this example, the end result I would want would be Spreadsheet 1 with the relevant matching values if the ID and Term match. So the ID 1111 is in both spreadsheets and has the TERM Fall in both spreadsheets, so I want to add the value 3.5 to spreadsheet 1. Then ID 2222 in the second row has the matching term Winter, so I want to pull in the value 4.0.

The result for this example would be this:

ID	TERM	VALUE
1111	Fall	3.5
2222	Winter	4.0

Currently I am trying to use a nested XLOOKUP and this is what I have but I always get the #Value error:

=XLOOKUP(A2, 'Spreadsheet 2.csv'!$A$2:$A$199039, XLOOKUP(E2, 'Spreadsheet 2.csv'!$E$2:$E$199039, 'Spreadsheet 2'!F:F))

The A column is the ID column and the E column is where my TERM column is in both of my real spreadsheets and the F column in Spreadsheet 2 is where the value I need is.

I am not great with XLOOKUP, so any help would be much appreciated! I am also happy to do another method, but XLOOKUP is the only one I am familiar with that will do this.

Thank you!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Rich~M 20,370 Volunteer Moderator

Here is my mock-up of your source data.

You can use this formula to retrieve the Value that matches the ID and Term criteria from the source data.

=XLOOKUP(1,('Spreadsheet 2.csv'!A2:A199039=A2)*('Spreadsheet 2.csv'!E2:E199039=E2),'Spreadsheet 2.csv'!F2:F199039,"Not Found")

A couple of notes:

All three ranges in the formula must have the same range. You used F:F in one.

I included the "Not Found" response if there is no match. You can remove that part of the formula if desired or enter a different response in the quotes.

0 comments

3 additional answers

Answer 1

Ashish Mathur 102.6K Volunteer Moderator

Hi,

In cell C2, enter this formula

=XLOOKUP(A2&B2,Sheet3!$A$2:$A$5&Sheet3!$B$2:$B$5,Sheet3!$C$2:$C$5,"")

Hope this helps.

0 comments

Answer 2

Would you be able to briefly explain why the lookup_value and lookup_array work like that?

=XLOOKUP(1,('Spreadsheet 2.csv'!A2:A199039=A2)*('Spreadsheet 2.csv'!E2:E199039=E2),'Spreadsheet 2.csv'!F2:F199039,"Not Found")

Why 1 instead of A2 for the lookup_value, and what the asterisk does in the lookup_array?

In order to look for a value using two criteria you have to identify that both criteria are met in the same row.

Each of the "equals criteria" for Column A and Column E create ranges of True or 1 and False or 0 responses.

The asterisk multiplies the two ranges against each other. Rows with two False are 0*0=0. Rows with one True are 1*0=0 or 0*1=0. Rows with two True are 1*1=1 or a True result for that row. So, when a 1 is entered as the Lookup Value, XLOOKUP looks for the first row in the range that results in a True or 1 result.

The formula then retrieves the data from the Return Array in that row. All three references must be for the same range on the same sheet so that each row can be evaluated as a unit.

Answer 3

My hero!! Thank you so much that worked absolutely perfectly. Would you be able to briefly explain why the lookup_value and lookup_array work like that? =XLOOKUP(1,('Spreadsheet 2.csv'!A2:A199039=A2)*('Spreadsheet 2.csv'!E2:E199039=E2),'Spreadsheet 2.csv'!F2:F199039,"Not Found")

Why 1 instead of A2 for the lookup_value, and what the asterisk does in the lookup_array?

I'm hoping to be able to use this in the future so I want to understand it a bit better, but I have never seen an xlookup like that, I would have expected a more complicated version of like =xlookup(A2, B:C, D:E) or something along those lines.

Feel free to ignore this question though if it is too much to explain - you 100% solved my issue!

Thank you again