Still no VLookup?

[TimmyCNHi]

The goal is simple: access a table, find a value of one column and match it with the value of another column in the same row. The road to get there in QS, however, doesn't seem to be so simple. Or is it?

Let's take this load script:

LOAD
    Type,
    State
FROM [lib://FRUIT_MANAGEMENT/FruitMan.xlsx]
(ooxml, embedded labels, table is "Basket");

Which generates the following table:

Type	State
Apple	Fresh
Pear	Rotten
Orange
Banana
Tomato	Fresh

I have a dashboard with a basket chart (yes, I just made that up), which looks like this:

The goal here is to colour the text boxes based on a fruit's state:
Fresh = Green (#00a708)
Rotten = Red (#ff002a)
(blank) = Blue (#0018ff)

Eg. If the apple turns rotten, its box colour should change from green to red on a data refresh.

In Excel this would be fairly simple:

=VLOOKUP(A2;A1:B6;2;FALSE)

Which would return: Fresh

So to make the colouring work it would need something like:

=IF(VLOOKUP(A2;A1:B6;2;FALSE)="Fresh";"#00a708";IF(VLOOKUP(A2;A1:B6;2;FALSE)="Rotten";"#ff002a";"#0018ff"))

Say I have the same table as loaded above, how could I make these colours work in my Qlik Sense basket chart visualisation? I could be using a Button and I'm trying to set the Background under Appearance 'By expression'.

Thank you for your time!

[jbhappysocks]

Your Button doesn't know that it is an Apple Button, you can't tie a dimension to the object, so it doesn't know what StateColor to use, you'll have to tell it. Try only({<Type = {'Apple'}>} StateColor) instead of just StateColor. 

[TimmyCNHi]

Yes! That's the one! Thank you, you guys are amazing!