Using XLOOKUP w/ SUMPRODUCT (array)

Question

Good afternoon,

I am trying to validate values by retrieving values from another sheet without creating additional helper columns.

In my lookup I need to combine Column A with Column J (RUD000057, RUD000067...) to return the corresponding value from the Hours Column

The Destination tab has the following: Position 179-Calgary, I can use CONCAT("000",LEFT(S1,3) to get "000179" and that would tie out to Column H from the Source Data. I need help with the second part of the lookup as described above. Destination Column U (M8V993279) would match a value from the combined Columns A+J above.

I can do this with helper columns, but looking for an XLOOKUP/Array (I think) to keep the original data unaltered.

Thank you,

Kala

Answer 1

Hi Kala,

Welcome to the Microsoft Q&A forum.

Based on your description, I understand that you would like to:

1/ Retrieve the Hours value from the source data worksheet and return it to the target worksheet.

2/ Perform the lookup based on two conditions at the same time:

• Extract the numeric code from the Position column, then standardize the code and match it against Timecard Work Department.
• Concatenate Payroll Company Code with File Number, then match the result against Alt. ID.

3/ When both conditions are met, Excel will return the corresponding Hours value.

4/ Perform the entire process using a single XLOOKUP or array formula, without creating any helper columns or changing the original data structure.

In my test environment, I used the following formula:

=@XLOOKUP(1,('Source Data'!R2C2:R1000C2="000"&LEFT(RC[-3],FIND("-",RC[-3])-1))*('Source Data'!R2C1:R1000C1&'Source Data'!R2C3:R1000C3=RC[-1]),'Source Data'!R2C4:R1000C4,"Not found")

If the conditions do not match, the formula will return "Not found".

I hope this information is helpful.

If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.

I look forward to continuing the conversation.

---

If the answer is helpful, please click "Yes" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

Use a multi-criteria XLOOKUP by building a single TRUE/FALSE array from both conditions.

If the source sheet has:

• Column A = Payroll Company Code
• Column H = Timecard Work Department
• Column J = File Number
• Column K = Hours

and the destination sheet has:

• S2 = Position like 179-Calgary
• U2 = Alt. ID like M8V993279

then the pattern is:

=XLOOKUP(
    1,
    (Source!$H$2:$H$1000=CONCAT("000",LEFT(S2,3))) * (Source!$A$2:$A$1000 & Source!$J$2:$J$1000=U2),
    Source!$K$2:$K$1000,
    "Not Found"
)

How it works:

1. CONCAT("000",LEFT(S2,3)) creates the department key from the position, such as 000179.
2. Source!$A$2:$A$1000 & Source!$J$2:$J$1000 combines values like RUD + 000057.
3. Each condition returns TRUE/FALSE.
4. Multiplying the two conditions converts the matching row to 1.
5. XLOOKUP(1, ...) returns the corresponding value from the Hours column.

If U2 contains only the file-number portion and not the company code, then use this version instead:

=XLOOKUP(
    1,
    (Source!$H$2:$H$1000=CONCAT("000",LEFT(S2,3))) * (Source!$J$2:$J$1000=U2),
    Source!$K$2:$K$1000,
    "Not Found"
)

Important checks:

• All lookup and return ranges must be the same size.
• Avoid mixing bounded ranges with entire-column references in the same formula.
• If matches fail unexpectedly, check whether values are stored as text versus numbers, especially where leading zeros are involved.

If a sum is needed for multiple matches instead of returning the first match, use the same multi-condition array pattern with SUM:

=SUM(XLOOKUP(
    (Source!$H$2:$H$1000=CONCAT("000",LEFT(S2,3))) * (Source!$A$2:$A$1000 & Source!$J$2:$J$1000=U2),
    Source!$K$2:$K$1000,
    0,
    0
))

That aggregation pattern is supported where XLOOKUP returns multiple matching values and SUM totals them.

---

References:

• XLOOKUP function | Microsoft Support
• XLOOKUP
• How to use XLOOKUP between two spreadsheets correctly? - Microsoft Q&A
• Excel formula - Microsoft Q&A