Formula to return Yes if Criteria matches and column has a 1

[Hannah122392]

Hello,

I need help with a complex (to me) IF statement. Here is the link to the sheet I am working on:

https://docs.google.com/spreadsheets...it?usp=sharing

What I need is on Friday Poles A13 - I need a formula to check Friday Poles D13:F13 against the Master Entry List A2:C2 if it matches, check M2 to see if it has a 1. If it does return a Yes, if not leave blank. If Friday Poles D13:F13 doesn't match A2:C2, check the next cell, so on and so forth.

Thanks for any and all help!

Edited to add: this is cross posted at the link below with the intention to delete the other post.
https://www.mrexcel.com/forum/excel-...ml#post5212961

[AliGW]

Please attach your file here (many members will not follow external links).

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.


Your thread title should be indicative of the problam you are trying to solve, not what you think the solution should be. Please change this accordingly before we proceed any further. Thanks!

[Hannah122392]

My apologies AliGW, I believe I have made your requested changes.

Thank you for your time!

[CDelSignore]

Is this what you're looking for?

=IF(AND($D13='Master Entry List '!$A$2,$E13='Master Entry List '!$B$2,$F13='Master Entry List '!$C$2,'Master Entry List '!$M$2=1),"Yes","")

[Hannah122392]

Yes and no. Yes that does part of it, but the problem is I need it to look down through Master Entry List until it does match.

[CDelSignore]

I see. I didn't realize that part. Try this:

=IFERROR(IF(AND(MATCH($D13,'Master Entry List '!$A$2:$A$20,0)=MATCH($E13,'Master Entry List '!$B$2:$B$20,0),MATCH($D13,'Master Entry List '!$A$2:$A$20,0)=MATCH($F13,'Master Entry List '!$C$2:$C$20,0),INDEX('Master Entry List '!$A$2:$M$20,MATCH($D13,'Master Entry List '!$A$2:$A$20,0),13=1)),"Yes",""),"")

What it does, is finds the row number within the range A2:A20 of the first name and compares it to the row number of the last name, and compares the row number of the first name to the row number of the name of the horse, and checks to see if there is a 1 in the M column at the same row as the first name. If any of those is false, the cell will be blank. If all 3 of those are true, it will return 'Yes.' The "IFERROR" part will prevent errors from showing up when you fill down to a row where there are no entry names.

One last addition: the last row of ranges used ($A2:$A20, for instance) will need to be adjusted if there are more than 20 rows of data. If there are 50 entrants, the column A range will be $A2:$A51, as long as there are no skipped rows.

I just checked something and it will only work if the first names are all unique. So, if there is more than one "Julie," it doesn't work. This will work until you get to that point. :/

[Hannah122392]

Ugh, thank you so much, but there will be a lot of duplicate first names

[CDelSignore]

Do the names of the horses tend to be unique and not duplicate? If so, then the AND conditions can compare the horse's name to the first name, the horse's name to the last name, and if there's a 1 in the same row as the horse's name at column M.

[Hannah122392]

Most of the time they are unique, but its not a guarentee

[CDelSignore]

Try this:

{=IFERROR(IF(MATCH(1,($D13='Master Entry List '!$A$2:$A$20)*($E13='Master Entry List '!$B$2:$B$20)*($F13='Master Entry List '!$C$2:$C$20)*(1='Master Entry List '!$M$2:$M$20),0),"YES",""),"")}

The {} (squiggly brackets) denote this is an array formula. You will either need to include them, or hit CTRL+Shift+Enter to enter the formula into the cell. If you just hit Enter, you'll end up with a result of #N/A.

[AliGW]

You will either need to include them, ...

No, it won't work if you type them in. You have to do it by entering the formula with CTRL+SHIFT+ENTER.

[FlameRetired]

What I need is on Friday Poles A13 - I need a formula to check Friday Poles D13:F13 against the Master Entry List A2:C2 if it matches, check M2 to see if it has a 1.

Since we are working in 'Fri Poles' sheet shouldn't that be D2? Your formula in J12 seems to indicate that.

With that thought in mind are you also wanting to apply this in all the other sheets 'Fri Poles' → 'Sun Masters'?

With the understanding all the above is true ... with the current sheet names in cell A1 of each sheet ... try this in A13 and fill down.

Please Login or Register  to view this content.

While the MMULT = 3 guarantees all 3 names match it will not tolerate typos. Incorrect results can return. The formula in J12 is a good error check. BTW that can be be made sheet specific if you are interested.

Also see the formula in cell A1. It returns the active sheet name. It can be applied with sheets grouped and used as a lookup reference.

BTW the headers in 'Master Entry List' do not always match the sheet names in question. Also the layouts of the sheets are not all consistent. I cleaned up the headers in the attached.