I'm trying to fill cells B2:C3 with relevant information from the two tables below.

The tables are identical except the 1st one is a proper dynamic Table (which is named Table4) and the 2nd one is just a range of values.

Currently I'm using an XLOOKUP on the 2nd (range valued) table. For example, the formula in cell B3 is:

=XLOOKUP($A3,$A$15:$A$21,XLOOKUP(B$1,$B$14:$E$14,$B$15:$E$21))

But I would like to use an XLOOKUP on the the dynamic Table and can't seem to find the right formula.

I know that the following would work for cell B3:

=XLOOKUP(A3,Table4[Column1],Table4[2021]) HOWEVER, I don't want to statically reference column 2021.

I thought this would work:

=XLOOKUP($A3,Table4[Column1],XLOOKUP(B$1,Table4[#Headers],Table4))

but I get an #N/A

I do know that if the headers were a text field instead of the number "2021" then the last formula I mentioned would in fact work.

CodePudding user response：

The INDEX MATCH Version: =INDEX(tblData,MATCH(A2,tblData[Column1],0),MATCH(B1,tblData[#Headers],0))

BUT: The year in B1 has to be entered as text --> put an apostrophe in front of 2021 etc.

CodePudding user response：

This worked best for me but it involved using Power Query where I turned my original Table into a transformed Table (which I named Clean_tbl) with the Year in a column going down and all values in another column.

I got to use XLOOKUP, a Table, and I didn't have to compromise the header formats (turn a year number into text) of the summary table.

The formula in F4 is as follows:

=XLOOKUP($E4&F$2,Clean_tbl[[#All],[Column1]]&Clean_tbl[[#All],[Year]],Clean_tbl[[#All],[Value]])