Help with Lookup formula

[Martina_Hawkins]

Would someone please help with the below formula as I can’t see what is wrong with it and its not working. I have uploaded an image to show table formats.

IFS(
[Supplier]=“ADA Fastfix - ADA02”,Lookup([Product],“Product List”,“ProductS”,“ADA Fastfix”,),
[Supplier]=“BSS - BSS01”,Lookup([Product],“Product List”,“ProductS”,“BSS”),
[Supplier]=“Wolseley - WOL01”,Lookup([Product],“Product List”,“ProductS”,“Wolseley”)
)

[LeventK]

@Martina
Try it like this:

SWITCH(
	[Supplier],
	"ADA Fastfix - ADA02", LOOKUP([_THISROW].[Product],"Product List","ProductS","ADA Fastfix"),
	"BSS - BSS01", LOOKUP([_THISROW].[Product],"Product List","ProductS","BSS"),
	LOOKUP([_THISROW].[Product],"Product List","ProductS","Wolseley")
)

[Martina_Hawkins]

Thank You, that seems to be working.

[WillowMobileSys]

Hmm, I’m curious… why the usage of [_THISROW] in the first two LOOKUPs but not the third?

[LeventK]

@WillowMobileSystems
It’s totally my bad…Thanks for your valuable attention…Fixed my post as well

[WillowMobileSys]

It wasn’t meant as a critique at all! I have just been trying to truly understand when [_THISROW] is required and when its not.

I use the rule of thumb “when there is ambiguity” such as the same column names between two data tables. But there seems to be outliers to this rule of thumb.

For instance, in this case there doesn’t seem to be any ambiguity so I don’t get why [_THISROW] is needed.

In other instances, when its not needed, the usage of it create problems - even though it seems it shouldn’t matter.

It leaves me a confused little man!!

[LeventK]

I believe the most competent person who can explain this is @Aleksi

[Aleksi]

The [_THISROW] is needed when you need to say for the SELECT expression to read the column not from the same table you specified in the SELECT. The ANY(SELECT(…)) experssion is typed…

ANY(SELECT(Product List[ADA Fastfix],[ProductS]=[_THISROW].[Product])), which is equal to…
LOOKUP([_THISROW].[Product],“Product List”,“ProductS”,“ADA Fastfix”)

If you type the SELECT expression like SELECT(Product List[ADA Fastfix],[ProductS]=[Product]), it probably won’t work if you don’t have the “Product” column at all in your “Product list” table. If you type it like… SELECT(Product List[ADA Fastfix],[ProductS]=[ProductS]), the contidion is always TRUE and you will have all values from that table.

So… the SELECT expression is not able to know from which table it should read the value, if you don’t specify it with the [_THISROW]. Think it like [_FROM_THIS_RECORD] and it reads the value where you have that formula).

[Martina_Hawkins]

Thanks everyone