How to assign assign condition using if statement and VLOOKUP

0 votes

I have the following data in an excel sheet named players

enter image description here

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:-

Draft Team

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 by narikkadan
• 63,420 points
186 views

1 answer to this question.

0 votes

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

enter image description here


• 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()

enter image description here


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

answered Mar 26, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

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

answered Feb 11, 2022 in Others by narikkadan
• 63,420 points
1,907 views
0 votes
0 answers
0 votes
1 answer
0 votes
1 answer

How to create and download excel document using asp.net

First, download the Open XML Format SDK 2.0. It comes ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
2,439 views
0 votes
1 answer

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

answered Nov 4, 2022 in Others by narikkadan
• 63,420 points
1,148 views
0 votes
1 answer

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

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

answered Nov 7, 2022 in Others by narikkadan
• 63,420 points
782 views
0 votes
1 answer

Multiple IF statements in Excel not working

When you add an IF statement to a formula ...READ MORE

answered Nov 12, 2022 in Mobile Development by narikkadan
• 63,420 points
250 views
0 votes
1 answer

IfError with else, does this function exist in Excel?

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

answered Dec 23, 2022 in Others by narikkadan
• 63,420 points
311 views
0 votes
1 answer

How to rename a workbook other than using (Name) and (FileSystemObject. MoveFile)?

Use  SHFileOperation API Option Explicit Private Declare PtrSafe Function SHFileOperation ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
197 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
295 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP