Excel: How to perform an XLOOKUP by using a column value as a search parameter

Question

In excel, I have the following table:

Month	Year	ProjectID	1-23	2-23	... 12-23
10	2023	14404	$210	$630	$3000
10	2023	15023	$250	$420	$5000

The monthly columns in the table represents monthly forecasts/actuals for each projects throughout the year (1-23 to 12-23). The Month and year columns represent the month in which the data was retrieved and they get updated each month and would look something like this:

Month	Year	ProjectID	1-23	2-23	... 11-23	12-2023
11	2023	14404	$210	$630	$450	$3000
11	2023	15023	$250	$420	$500	$5000

Let's say I wanted to perform a search for the forecast/actual value for a specific month and year using the xlookup function. Using the month and year columns (which are subject to change) how can I perform an xlookup to search for the monthly actual/forecast for the month that I'm in (i.e. if the Month is "11", and the year is "2023", I want to get the forecast/actual value in the [11-23] for each project.)?

Answer 1

Consider the following example and modify it to meet your needs:

The values to filter by Month and Year in this example are located in cells A8 and B8 respectively, and the formula used in cell A10 is:

=LET(
   val, TEXT(DATE(B8, A8, 1), "m-yy"),
   hdr, HSTACK(A1:C1, val),
   arr, HSTACK(A2:C5, XLOOKUP(val, TEXT(D1:O1, "m-yy"), D2:O5)),
   VSTACK(hdr, FILTER(arr, (INDEX(arr,, 1)=A8)*(INDEX(arr,, 2)=B8)))
)

This formula assumes the values in the header range D1:O1 are dates with custom formatting "m-yy" applied. The TEXT function is used to convert the values to text strings, so the XLOOKUP function can return a match.

If the values in the header range D1:O1 are already text, then simply use XLOOKUP(val, D1:O1, D2:O5) to achieve the same results.