Looking up value from a column, using another field as column lookup

[JamieV]

Hi, I had a search around for something along these lines but haven't been successful

I have a column of data with a "key" (reference) value. Then I have a number of columns, one of which will be titled the "key" value. I want to return the matching value into a new field at the end of the row. Example is as follows:

Key	Purple	Red	White	Pink	Green	Orange	Blue	Black	Match
Green	1	2	3	4	5	6	7	8	5
Blue	8	7	6	5	4	3	2	1	2
Red	2	4	6	8	10	12	14	16	4
Yellow	1	3	5	7	9	11	13	15	NULL

How would I go about this?

Kind regards,

Jamie

[Qiu · Accepted solution]

@JamieV 

We can use a Transpose tool to make the columns expect "Key" column to be vertical then filter out the matching.

[JamieV]

Thank you Qiu, this worked perfectly for me.

[Qiu]

@JamieV 
Good to know it helps. 😁