Forum Discussion
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 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?
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.
4 Replies
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.
- Thanks Rini, Because i used different sheets i used the formula of Hans, but yours Works too!
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.
