How to assign assign condition using if statement and VLOOKUP

Question

I have the following data in an excel sheet named players

In a new sheet I have to filter the draft team name based on criteria of start match and end match

The data with the expected out should be highlighted as below:-

The criteria for the Draft Team are based on the new data's Match column and the players' sheet's Start Match and 'Finish Match' columns. Due to the Match not falling inside the Start Match and Finish Match, the Draft Team for players G and F is blank.

How do I obtain the output that has been highlighted?

Answer 1

You can use either XLOOKUP() or INDEX() with MATCH() Function.

---

• Formula used in cell L2

=IFERROR(INDEX($C$2:$C$10,
MATCH(1,(I2>=$F$2:$F$10)*(I2<=$G$2:$G$10)*(J2=$A$2:$A$10)*(K2=$B$2:$B$10),0)),"")

---

Or, with XLOOKUP()

---

• Formula used in cell L2

=XLOOKUP(1,(I2>=$F$2:$F$10)*(I2<=$G$2:$G$10)*(J2=$A$2:$A$10)*(K2=$B$2:$B$10),$C$2:$C$10,"")

---