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?

Mar 26, 2023 in Others 169 views

## 1 answer to this question.

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,"")
```

• 38,010 points

## How to set meta tags using Angular universal SSR and ngx-seo plug-in?

first Install the plug-in with npm i ngx-seo ...READ MORE

## How to set Bullet points colored in unordered and ordered lists in html via CSS without using any images or span tags

I want to differentiate the lists using ...READ MORE

## Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

## (Excel) If cell is greater than <condition> then minus <number>

The IF function to calculate B5 (amount ...READ MORE

## IfError with else, does this function exist in Excel?

Next to IFERROR(), there also is the ISERROR() function, which ...READ MORE