Formula LOOKUP

[Izzat_Safrah]

---

I want to use the formula ( LOOKUP ) in the table ( CUSTOMER ) in the column [STATUS] to know the customers latest status from report table ( DESIGNS )

I tried the following formula and it doesn’t do the required

LOOKUP([KEY-C],"DESIGNS","KEY-D","STATUS")

What is the correct formula that searches for the status (JOB).
If it couldn’t find (JOB) then it would search for the status (EDIT)
If it couldn’t find (EDIT) then it would search for the status (SKETCH)
And finally, If it couldn’t find (SKETCH) then it would write the phrase (NON)

[LeventK · Accepted solution]

@UNITED_SKATEBOARDS
LOOKUP expression always returns the first value found from the table. To achieve the desired result, create a Virtual Column with a MAXROW(…) expression in your CUSTOMER table first. Name it as you like i.e. [LATEST_STATUS]

MAXROW(
    "DESIGNS",
    "DATE",
    ([KEY-D] = [_THISROW].[KEY-C])
)

Now you can de-ref the above expression for the Initial Value of your [STATUS] column in CUSTOMER table

IF(
    ISNOTBLANK([LATEST_STATUS].[STATUS]),
    [LATEST_STATUS].[STATUS],
    "NON"
)

[Izzat_Safrah]

@LeventK Thank you for this help it work perfect