How to specify the column name for a return array using a cell reference in an xlookup formula

Question

I have a table that has a material number in column A and the ending inventory counts for that material in the columns that follow. The columns are titled with the month end date (12/31/25, 01/31/26, 02/28/26, etc.).

I would like to use Xlookup in another table to provide the appropriate ending inventory for a specified material number and a specified month end date. Not using a cell reference, the xlookup formula would be:

=XLOOKUP([@[Ink Number]],InkMaster[INK'#],InkMaster[12/31/2025])

I would like to reference a cell that allows the user to specify the month end date (ie 01/31/2026) and have that dynamically change the return array so that it returns that column instead of the one titled "12/31/2025"

Answer 1

Hi,

In cell B10, enter this formula

=XLOOKUP(A10,A2:A6,XLOOKUP(B9,B1:D1,B2:D6))

Hope this helps.

Answer 2

Hi @Dianna Stoffel,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your sharing, the formula you have tried from Q&A Assist reply is working as expected since in Excel Table, the items in the headers (including InkMaster[#Header]) are text strings even if they display like dates. The MATCH function cannot match text (e.g. "01/31/2026") to numeric 01/31/2026 so you will get #N/A error and it will return correct when you change the reference cell to text.

So, you can keep the reference cell as dates and convert it to text inside the formula by using:

=XLOOKUP([@[Ink Number]], InkMaster[INK'#], INDEX(InkMaster, 0, MATCH(TEXT(B1,"mm/dd/yyyy"), InkMaster[#Headers], 0)))

I hope this helps you move forward smoothly. If you have any questions or need further assistance, please feel free to reach out again on this post so I can continue to support you.  

Thank you for your patience and understanding. Looking forward to your response.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".       

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 3

This solution works, except that it returns #N/A when the fields are all set to date format. The headings are dates and so is the reference cell. When I change the reference cell to text, it returns the correct values, but only if the date is typed exactly as it appears as the heading.

The reference cell needs to be a date as it also is used in another formula that pulls data between a specified range where that cell is the beginning of the date range.

Answer 4

To specify the column name for a return array using a cell reference in an XLOOKUP formula, you can use the INDEX and MATCH functions to dynamically reference the column based on the month end date specified in a cell. Here’s how you can structure your formula:

Assuming:

• The material number is in column A of your InkMaster table.
• The month end date you want to reference is in cell B1.

You can use the following formula:

=XLOOKUP([@[Ink Number]], InkMaster[INK'#], INDEX(InkMaster, 0, MATCH(B1, InkMaster[#Headers], 0)))

In this formula:

• MATCH(B1, InkMaster[#Headers], 0) finds the position of the month end date specified in cell B1 within the headers of your InkMaster table.
• INDEX(InkMaster, 0, MATCH(...)) returns the entire column corresponding to the matched month end date.

This way, when you change the date in B1, the XLOOKUP will dynamically return the appropriate ending inventory for the specified material number and month end date.