In the following example, Col1 in the condition statement is formatted as picklist and RefTable[RefCol] as text.
Above formula returns #VALUE! instead of a correct result because FINDROW condition interpreter fails to convert a picklist datatype to a datatype that can compare to the datatype on the other side of the operand.
operand1(datatype picklist) = operand2(datatype text)
In the following formula:
By rewriting “[Req Type]” as “LEFT([Req Type],LEN([Req Type]))“, we are forcing a conversion from picklist to text :
=FINDROW(_Req_Type_5,"_Req_Type_5[Name]=LEFT([Req Type],LEN([Req Type]))")[Process]
This workaround solved the issue! Which proves the existence of the bug.