Using an IF function to cross reference different tabs with lots of criteria

I have attached an example of what I'm trying to figure out. You can see my desired result on the last tab.

I'm trying to use an IF function with lots of criteria that cross references a table on another tab to place a "YES" or "NO" in each cell if the criteria is met. You can find the criteria on the example. Can someone please help me as I've tried everything!




I posted this post on multiple other sources (https://www.mrexcel.com/forum/excel-...ease-help.html, http://www.excelguru.ca/forums/showt...-(PLEASE-HELP))

Glenn,

Thanks for your help. It's getting there, but not quite yet. There is only one thing I can't get to match up.

When I place the formula into my big spread sheet, it is picking up every single cell that has an "X" in it, but I need it to only pick up the ones that have an X AND has "Buyer" in the corresponding column for each team (column C). I tried adding another MATCH("Buyer", etc. etc.), but it's not picking it up. What should I do?

Also, I changed one criteria of your formula as the position doesn't pertain to the actual player, but rather the position of need for the team, so this is what I have, but the last match isn't working"

=IF(E6="","", INDEX('Seller - Potential Teams'!$D$6:$L$29,MATCH(F6,'Seller - Potential Teams'!$B$6:$B$29,0),MATCH(E$6$, Seller - Potential Teams'!$D$5:$L$5,0), MATCH("Buyer",'Seller - Potential Teams'!$C$6:$C29$,0)))

Originally Posted by Glenn Kennedy

It took a while to understand your requirement, but is this it?

=IF(E6="","",INDEX('Seller - Potential Teams'!$D$6:$L$29,MATCH(F6,'Seller - Potential Teams'!$B$6:$B$29,0),MATCH(I6,'Seller - Potential Teams'!$D$5:$L$5,0)))

in D6, copied down?

Glenn, can you specify specific words for a MATCH to look for, such as "Seller"? I know this is possible for most functions, but it doesn't look like it's doing that for this function I've tried to write. Thank you so much for your help.

Originally Posted by Glenn Kennedy

I can't really tell what you've done, 'cos I can't see your data. can you please post a sample of what you are REALLY working with. However, you CANNOT add a third MATCH function. The first is there to establish the row and the second the column of the matching result.

I can't post the actual data as it is of confidential matter. The data is exactly the same though, but just adjusted to make sure it is confidential. The original function you attached was exactly right except for the fact it didn't take into account the "Buyer" or "Seller" classification that I mentioned above. I really appreciate your help and you are the only one who has gotten close to the solution, so please let me know what other information you need to help figure this out.

Is there anyway I can add an AND function to the INDEX MATCH to specific that they INDEX MATCH has to have an "X" in the cell AND the player's team must equal "Buyer" (I added this column to my data for each player using a v-lookup