Hello! I got confused, because this formula now requires some additional values to be entered ([leave_original_value],[replace_nulls],[ignore_case]). And I can choose from TRUE or FALSE values.

Also, if I want to update the old LookupEx formula it doesn’t work - I get an error (attached). Formula I'd like to update - =LookupEx(Category,Timetracker Exclusion Cost Pools Raw,Category,Cost Pool).

How should I use this formula from now on?

Thank You!

LookupEx() function syntax has not changed.

The last three function arguments are optional and can be omitted if you don't need them: [leave_original_value],[replace_nulls],[ignore_case].

The function in your screenshot is using correct syntax, so you may want to try deleting and retyping the formula from scratch. Not sure why you're seeing an error message.

Thanks! Any ideas what does the error message mean? Because I've also tried to write a totally new LookupEx formula, but the error is still present.

The error message suggests a syntax error: It says the first parenthesis in your formula is incorrect.

But clearly your formula is fine, so something else seems amiss.

Apptio's support team can help if the issue persists.

I've run into that whenever using the preview window with LookupEx - I'll show an error, yet if I save it anyway and look at the data in the table just below it with no errors.  Think it's a bug..

Hm.. This is true. And what about using IF statement with LookupEx? Will it work? I.e.: =If(Column1="xxyy",LookupEx(Column1,TableMapping Transform,Column1 from TableMapping Transform,Column2 from TableMapping Transform),"")

Is it possible to use LookupEx only expanding part of the table?

Because I get an error even after saving.

LookupEx() is a powerful function but, as you pointed out, it has a few limitations:

1. It does not show correct results in the formula preview window.

2. It cannot be combined with most other functions, such as If().

3. Only one LookupEx() per data table is supported. Using two or more might cause unexpected results because of the order of operations.

As @Chris Davidson mentioned, you shouldn't put lookupex or lookup in an IF statement.

You are better off creating separate columns:

LookupEx Helper=if(Column 1="xxyy",Column 1,"No Lookup")

Column 2=lookupex(LookupEx Helper,TableName,Column Name,Return Column Name)

We use "No Lookup" to prevent the lookupex from working.

Secondly if you want to do multiple levels of Lookup, I would say do it in transforms. So you do the first lookupex in Dataset A. Then take a transform (Dataset A Stage 2) and do the second lookupex. That way you control the order of operations.

BTW - remember that lookup and lookupex is case sensitive. so I tend to use the: optional parameters in the following way:

=lookupex(LookupEx Helper,TableName,Column Name,Return Column Name,false,false,true)