Xlookup with multiple lookup arrays

Question

I am having issues using Xlookup with multiple lookup arrays. I'm using a single lookup value (A1) with columns C2:C73....across to AB2:AB73 with the return array being B2. The goal is to look up an original sales order (C) using the serial numbers (D-AB) for reference and then return said sales order.

Example:

=XLOOKUP(A1,D2:D73&E2:E73&F2:F73&G2:G73&H2:H73&I2:I73&J2:J73&K2:K73&L2:L73&M2:M73&N2:N73&O2:O73&P2:P73&Q2:Q73&R2:R73,C2:C73)

This formula will not return any value outside of the first lookup array, is this a syntax error or do I need to add another formula? Any help would be appreciated!

dscheikey · Answer

johntitan&nbsp;Hi, you can solve this with MMULT(). A nice trick from HansVogelaar.Attached is my example document.

johntitan · Answer

dscheikey&nbsp;thanks for the support, I will give this a try and see how it goes

Forum Discussion

Xlookup with multiple lookup arrays

Share

Resources