LOOKUPVALUE on the same table with conditions for the search and filters for the results

[sebasj]

Hello everyone

I have a table with the following structure

id	v1	v2	v3	v4 (expected)
1	a	a	X	P
2	a	a	Y	Q
3	a	a	Z
1	b	a	P
2	b	a	Q
3	b	b	R

As you can see, IDs are repeated. So,

- if for a given id, v2 = a, then I'd need a formula that searches for the id (on the same "id" column), and if there's a match and v2=b and v3=a, then retrieve the value in v3 to v4 into the row where v2=a.

I've filled V3 with the expected values because it's much easier to get an idea of the expected results rather than my poor explanation. I know it can be done by just duplicating the table, adding a relationship and using RELATED but I'd prefer not because the idea is that the tables are updated from a OneDrive location, so I'd need to duplicate the table in source, add several calculated columns, etc.

I tried to make a LOOKUP and a calculated column but I miserably failed, so any input will be more than welcomed.

Thanks in advance!

Best as always

Seb

[amitchandak · Accepted solution]

@sebasj , a new column

Try Like

if([V2] ="a", maxx(filter(Table, [ID] = earlier([ID]) && [V2] = earlier([V2]) && [V2] ="b" ), [V3]), blank())