SUMIFS alternatives to return text

[vik1987]

I have a vast amount of data, and I want to return a specific cell's text value if certain criteria in other columns are met. I can use SUMIFS to get it to work for values (numbers) but cannot apply a similar theory for text. Probably easier explained by much simplified example below... I feel like INDEX and MATCH may be relevant, but I think MATCH returns the row reference for the first occurrence of the criteria (i.e. the first "Chris") whereas the first "X" may not be in the same row

[TABLE="width: 707"]
<colgroup><col><col span="2"><col><col span="7"></colgroup><tbody>[TR]
[TD]Sheet 1:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]X[/TD]
[TD]5[/TD]
[TD]TextA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]TextB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]TextC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]TextD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]X[/TD]
[TD]11[/TD]
[TD]TextE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]TextF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]TextG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]X[/TD]
[TD]16[/TD]
[TD]TextH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2:[/TD]
[TD]Chris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]16[/TD]
[TD="colspan: 9"]=SUMIFS('Sheet1'!C3:C10,'Sheet1'!B3:B10,"x",'Sheet1'!A3:A10,B12), where B12 is "Chris"[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD="colspan: 10"]Need Column D's Text based on same criteria as above (i.e. If Column A is Chris, Column B has an "X", then return column D's text)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Try

=INDEX('Sheet1'!D3:D10,MATCH(1,INDEX(('Sheet1'!A3:A10=B12)*('Sheet1'!B3:B10="X"),,),0))

 

[vik1987]

Amazing! Thanks v. much - trying to follow the logic... what does the "1" in the MATCH 'array' do, and how does it rely on the subsequent INDEX formula (with no row / column references)? Is the index formula some sort of cross-referencing syntax?

Again, thanks so much!

 

[Jonmo1]

This part creates an array of 1's and 0's
INDEX(('Sheet1'!A3:A10=B12)*('Sheet1'!B3:B10="X"),,)
Goes through each row 3 to 10
Has 2 true or false questions for each row, is A=B12 is B="X ?
And those 2 true/false results are multiplied for each row.
TRUE*TRUE = 1
Any other combination of true/false * true/false = 0

Then MATCH finds the first 1.
And indexes columnd D based on the Match.