Custom column to return value by comparing the row value to the header name-CodePudding

I need to create a new column %age that returns the value by taking the value in the Subject column of that row to find the value in the column name that matches the value in the subject column.

For example,

K1 = =HLOOKUP([@Subject], E:H,2,0), but it's wrong as in column L

How to get the formula to return that row values as in column K?

Regards, NewB

CodePudding user response：

In your example, paste the following formula in K2:

=XLOOKUP(I2,E$1:H$1,E2:H2)

which should look as illustrated here:

CodePudding user response：

In powerquery, add column .. custom column .. with formula

= Record.Field(_, [Subject])