Vertical search of multiple variables has to return a different value depending column data found

[jan970]

Hello,

I'm doing a search to find the % that belongs to a team ( fe Anderlecht) coupled to a matchday (fe 1)

(table 1 => looking to fill cell N/B marked in yellow)

The data comes out of sheet 2 but If the team is found in column "Thuisploeg" then the percentage out of column 3 DEF % Thuis should be returned. IF the team is found in column "Uitploeg" then the percentage out of column 4 Def % UIT should be returned.

Any experts that can help me?

Kind regards,

Jan

 

[#2 · Accepted solution]

@jan970 

It could be

=XLOOKUP(A3, [Thuisploeg], [Def % Thuis], XLOOKUP(A3, [Utiploeg], [Def % UIT]) )

[#3]

I get an error when entering
=X.lookup(A3;Speeldagen!$B$2:$B$9;Speeldagen!$C$2:$C$9;X.lookup(A3;Speeldagen!$E$2:$E$9;Speeldagen!$F$2:$F$9;0;0;1);0;1)

[#4]

when I enter the first X.lookup and 0 if not found it works but If I try to enter both x.lookups it doesn't work

and one more thing. 🙂

I would like to add the matchday as variable as well. Because the percentage differs per team from matchday to matchday

[#5]

@jan970 

Perhaps you may share sample file removing sensitive information?