Help on XLOOKUP array ranges

Occasional Contributor

Hi -

I have my customer master list with account numbers in one sheet in my workbook, and then I have each month as another tab.  I capture my monthly sales in the month tab. I want to type in the customer name in the month tab and have it return the account number.  When I apply the formula to the rest of the account number and customer name columns in the month sheet, the formula range changes.  For example, if the range was C1:C8 when creating this formula in the first account number cell, when I copy the formula to the second column cell, it changes to C2:C9.  I need each row to use the entire range for the account number and the client name.  I manually changed the ranges in the formula bar to get the correct info.  In my example, I have two cells that reference a client name that is earlier than the range in the formula when I copied it from the first cell and pasted it into that cell. It returns an error message.  Is there another way to paste the formula into the rest of the cells/rows, so that it keeps the same initial range array?  Thanks.


2 Replies
best response confirmed by Hans Vogelaar (MVP)

@jackiebiggs Use table column instead of cell reference. Try-

=XLOOKUP([@[Customer Name]],CustomerList[Customer Name],CustomerList[Customer ID])


Wonderful! How did you create this formula? I'm not sure what a table column is. Thanks!