Sheet update filter, not capturing formula value

Hello,

I have a sheet update task. Where there is a filter on matching of a parameter color.

color = IF( formmultirow5.count = 0 , LOOKUP([PC_PrePackDef.Color] , PC_PrePackDef.unique_id = formmultirow5.ppdefuniqid_epp) , LOOKUP([PC_PrePackDef.Color] , PC_PrePackDef.unique_id = formmultirow5.ppduniqid_cpp))

image

But this filter is not working. I have marked the " Insert new record if it doesnt exist"
there the color parameter is given the same formul.

The filter doesnt work and a new record is created with correct value in color cell.

image

SO the formula, is working when updating a sheet record, but not when used as a filter.

IF( formmultirow5.count = 0 , LOOKUP([PC_PrePackDef.Color] , PC_PrePackDef.unique_id = formmultirow5.ppdefuniqid_epp) , LOOKUP([PC_PrePackDef.Color] , PC_PrePackDef.unique_id = formmultirow5.ppduniqid_cpp))

Any suggestions?

I tried a workaround, Using a variable,

But the variable is also not able to capture any thing.

the formula used for setting variables value is same as formula used to update a record in a sheet.
it works in the sheet but not when assigned to a variable.

@yash1

Since LOOKUP of Single Column will return List, so even if the LOOKUP is returning a single value it is List of 1 item, so you can use INDEXVALUE() function to get the first index and then assign to color variable.

For Example -
INDEXVALUE(list,1)

Hope this Helps!

thank you! this helps.