Return name based on 2 criteria

[QUESTIONS123]

Hi ALL,

Would someone be able to help me with my formula. I would like to have a name return based on 2 critera accross 2 worksheets. Any help will be greatly appreciated.

[tigeravatar]

QUESTIONS123,

Welcome to the forum!
Attached is a modified version of your example workbook. Is something like that what you're looking for?

[QUESTIONS123]

Thank You tigeravatar!!! This is exactly what I was looking for. Would you mind telling me why the "1" needs to be in front of the nested Index function?

=IFERROR(INDEX(DATA!$B$2:$B$6,MATCH(1,INDEX((DATA!$A$2:$A$6=$A7)*(DATA!$C$2:$C$6=0.5)*(DATA!$B$2:$B$6<>D7),),0)),"")

[tigeravatar]

The 1 is part of the Match formula. Specifically, Match is looking for the first 1 to appear from the resulting Index. The index formula does true/false checks based on the criteria. True evaluates to 1 and False evaluates to 0. Anything that's true in all of the checks will be 1*1*1 = 1, and anything that has any falses will result in 0. So the match finds the first item that passes all of the conditions and returns that position number to the original index, which provides the formula result.