May 22 2021 02:00 PM
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 with processing times (Column O).
If a text is selected in table 1 I want the corresponding processing time to be retrieved from table 2
and shown in column K in table 1.
Can anybody help me with this puzzle?
May 22 2021 02:35 PM - edited May 22 2021 02:36 PM
SolutionIn 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.
May 22 2021 10:21 PM
@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.
May 23 2021 02:59 AM
May 23 2021 03:02 AM
May 22 2021 02:35 PM - edited May 22 2021 02:36 PM
SolutionIn 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.