Hlookup formula help

Try ...

=IFS(D2=$F$1,F2,D2=$G$1,G2,D2=$H$1,H2)

See sheet 2 attached
Lookup-formula.xlsx

I tried adding another column    ,D20=$I$1,I20   however I got a #NAME? error
pls see attached
Thanks Fabi
Lookup-formula.xlsx

I don't see another column in your attachment, but if you need to expand the range then you'll need to update the formula.  For example, if you add column I, then

IFS(D2=$F$1,F2,D2=$G$1,G2,D2=$H$1,H2, D2=$I$1, I2)

Sorry, I attached the old file. Here is the one I modified
Thanks
Ian
Lookup-formula.xlsx

here is a sample with the added column
Lookup-formula--2-.xlsx

Your worksheet doesn't work because you didn't modify the formula to include the added column.

You need to add the I column to the formula - like above - overwrite the old formula with the new one for all cells in column E

I'm at a loss as to why it still produces a #NAME? error when I copy your formula to an exact
copy on my worksheet.
Here is my latest sheet after copying your formula
Lookup-formula.xlsx

I don't see a #NAME? error on this attachment.

IFS is a XL2016 function and won't work in older versions.

That's weird, as it is the same file.
I'm wondering if there is something wrong with my Excel settings
but know little about that.
I've attached again with a file name change
for double checking.
appreciate your help
Ian
Name-error-file.xlsx

My version is 2016

Windows or Mac? I think it's only available on Windows versions.

In any case, you don't need to use IFS or add an extra column if you use my formula above.

Thanks Wayne, it works perfectly :)
Ian