Xlookup with multiple lookup arrays

New Contributor

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!

johntitan_0-1662569459830.png

 

 

 

 

2 Replies

@johntitan 

Hi, you can solve this with MMULT(). A nice trick from @Hans Vogelaar.

Attached is my example document.

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