Xlookup - Tables and Referencing a cell

Hi

This xlookup works =XLOOKUP(A4,Table1[Employee ID],Table1[PM Certification])

However I have several columns and don't want to keep changing the "PM Certificate" for each column I want to reference. I want to use the cell which has that work in it.

I have tried =XLOOKUP(A5,Table1[Employee ID],"Table1["&G2&"]") which returns a #VALUE error.

Can you help with the syntax - thx

Hi - can you attach your file?

If you have the field name in cell G2,

Formula:

=XLOOKUP(A4,Table1[Employee ID],INDEX(Table1[#Data],0,MATCH(G2,Table1[#Headers],0)))

Tangential rant. IF MSFT had ever revised DGET to be similar to @DGET from Lotus 1-2-3 Release 3 or later, one could use something like

=DGET(Table1,G2,"[Employee ID]="&A4)

Then again, if MSFT were willing to allow the 3rd argument to DGET to be an array, as in Google Sheets, it'd be possible to use

=DGET(Table1,G2,{"Employee ID";""}&LEFT(A4,{0;255}))

Alas, MSFT's Excel Developer Team is still severely afflicted with NIH Syndrome. At least they were willing to steal FILTER, SORT and UNIQUE from Google Sheets.