Forum Discussion

Copper Contributor

May 22, 2021

Solved

Excel formula

Hallo, I am trying to achieve the following: I have an input form (table 1) where you can select texts using a drop down. In another table (table 2) I have the same texts (Column K) and a column ...

excel

office 365

HansVogelaar
May 22, 2021
Raymond1093

In K8:

=IFERROR(VLOOKUP(L8, 'Sheet Name'!$K$107:$O$110, 5, FALSE), "")

In Dutch:

=ALS.FOUT(VERT.ZOEKEN(L8; 'Sheet Name'!$K$107:$O$110; 5; ONWAAR); "")

where Sheet Name is the sheet with the lookup table. If it is the same sheet, you can omit the part

'Sheet Name'!

Fill or copy down from K8 to K27.

Riny_van_Eekelen

Platinum Contributor

May 23, 2021

Raymond1093 As a variant, and since you tagged your post with Office 365, you could perhaps use XLOOKUP (X.ZOEKEN).

=XLOOKUP(L8,$L$100:$L$200,$O$100:$O$200,"",0)

or

=X.ZOEKEN(L8;$L$100:$L$200;$O$100:$O$200;"",0)

... assuming all ranges are in the same sheet and that the job codes are listed somewhere between rows 100 and 200. Would even be more dynamic if you named the ranges like "werksoort" (for L100:L200) and "tijdmeting" (for O100:O200). That makes the formula easier to read and maintain and it doesn't matter if the lookup ranges are in a different sheet.

Raymond1093
Copper Contributor
May 23, 2021
Thanks Rini, Because i used different sheets i used the formula of Hans, but yours Works too!