I'm using the LOOKUP expression to find the v...

[Eli_Allan]

I’m using the LOOKUP expression to find the value of a column named Type and matching that text with a table of values to find the correct Rate for each Type. This was working great until I realized that the Type column needed to be an enumerated list. Lookup can’t compare list values with text values…

Is there a different expression I can use here?

This is what I had. Calculated Rate=LOOKUP([Type], “Enumeration”, “Work Item”, “Rate”)

[Aleksi_Alkio]

If you need to have one “Rate” only, it will find only one (the first one) rate but with a different formula. Main question is what do you want to have with the lookup? Sum of those rates if more than one “Type” is selected? Something else?

[Steven_Coile]

It’s not clear what you now want to do.

I’d guess, now that [Type] is an EnumList, you want to see if the value of [Work Item] occurs within (rather than is equal to) [Type]? And if so, return the value of [Rate] for that one (and only one) row?

[Eli_Allan]

Thanks for the responses. I figured it out. But my initial description didn’t make it all that clear what I was looking for. So I’ll explain where I was at and how I solved it in case it helps anyone else.

The LOOKUP expression searches for a text value, it doesn’t support list values. To solve this I just needed to add a TEXT expression to convert the list value back to a text value.

Corrected expression: =LOOKUP(TEXT([Type]), “Enumeration”, “Work Item”, “Rate”)

The lookup expression now searches the current List value of [Type] and converts it to text. If this text matches a value in the worksheet “Enumeration” under the column Work Item it returns the corresponding Rate. I then use a virtual column to dynamically display corresponding costs and projections for varying [Type] values