FINDROW() bug does not match cells formatted as picklist

[FlyingZebra]

In the following example, Col1 in the condition statement is formatted as picklist and RefTable[RefCol] as text.

=FINDROW(RefTable,"RefTable[RefCol]=[Col1]")[Col2]

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)

check example:

In the following formula:
=FINDROW(_Req_Type_5,"_Req_Type_5[Name]=[Req Type]")[Process]

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.

[Matt_N]

Thanks for this feature request post @FlyingZebra ! Your feedback is important to us as we continue to find ways to improve experiences for our users. I'll bring this feedback to our team about comparing picklist vs plain text formats.

Glad you were able to find a method for your formula, but also wanted to share another way to accomplish this. Since you are unable to compare two different formats, what you can do is retrieve a rowlink’s column (dereference). In you case, you can rewrite this formula =FINDROW(_Req_Type_5,"_Req_Type_5[Name]=[Req Type]")[Process] to this instead:

FINDROW(_Req_Type_5,"_Req_Type_5[Name]=[Req Type][Name]")[Process]

What this does is pull the [Name] data from the _Req_Type_5 table for the rowlinked column [Req Type]. Since the [Name] data from the _Req_Type_5 table is text format the formula should work as well.

If you have any further feedback, let us know!