Triple criteria lookup

[sirmixalot]

Guys, I have been breaking my head on this and can't think of a helper column idea for some reason. Please let me know if I can clarify anything further.

Goal: Fill in Template in Picture 2 (the yellow part) based on the following constraints

Obtain values from Column B such as "bm-gq3a" etc based on the 3 criteria below

Column A with header "#" (Match to 0 or 1 or 2 in Row 3 in picture #2)
Columns C-F = "Yes" AND Columns G-K = "Yes" for the same row.


Example output provided in picture #2.

PICTURE 1 : Source Data

Picture 2: Template to fill in. solution filled in.

For example:
O16 = bm-gq3a is there because:
1st criteria: the value in O3 matched to the value in cell A4
2nd criteria: Loganville being "Yes" in cell H4
3rd Crtieria: plan "bm" being "Yes" in D4

Similarly, S16 = bm-gq3b because of value of 1 in cell S4 matching value of 1 in cell A5

 

[jasonb75]

Perhaps

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$4:$B$8)/($A$4:$A$8=O$3)/(INDEX($G$4:$K$8,0,MATCH($M16,$G$3:$K$3,0))="Yes")/(INDEX($C$4:$F$8,0,MATCH($N16,$C$3:$F$3,0))="Yes"),1),"")

I think that the syntax is correct but can not test the formula on a screen capture. XL2BB is preferred for posting examples to the forum (click the button on the reply toolbar for more information).

 

[sirmixalot]

		Plans				Locations
#	GB	bw	bm	vm	oc	Augusta	Loganville	Destin	Westin	France
0	bm-gq3a	No	Yes	No	No	Yes	Yes	Yes	Yes	No
1	gm-gq3b	No	Yes	No	No	Yes	Yes	Yes	No	No
2	bm-gq3c	No	Yes	No	No	Yes	Yes	Yes	No	No
3	bm-gq4a	No	Yes	No	No	Yes	Yes	Yes	Yes	No
4	san-fb-123t	No	Yes	No	No	Yes	Yes	Yes	Yes	Yes

		0	0	0	0	1	1	1	1	2	2	2	2
location	plan	attached_plan[id][0]	attached_plan[quantity][0]	applicable_plan[id][0]	attached_plan[type][0]	attached_plan[id][1]	attached_plan[quantity][1]	applicable_plan[id][1]	attached_plan[type][1]	attached_plan[id][2]	attached_plan[quantity][2]	applicable_plan[id][2]	attached_plan[type][2]
Augusta	oc
Loganville	oc
Destin	oc
Westin	oc
France	oc
Augusta	vm
Loganville	vm
Destin	vm
Westin	vm
France	vm
Augusta	bm
Loganville	bm
Destin	bm
Westin	bm
France	bm

So I managed to apply it, just one ")" parentheses missing on the Aggregate function I believe - i have marked in bold where I think it should be. It is resulting in a #SPILL error.


=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$4:$B$8)/($A$4:$A$8=O$3))/(INDEX($G$4:$K$8,0,MATCH($M16,$G$3:$K$3,0))="Yes")/(INDEX($C$4:$F$8,0,MATCH($N16,$C$3:$F$3,0))="Yes"),1),"")

CC: @jasonb75

 

[jasonb75]

Nothing missing on the Aggregate functions, it was missing from the end of Index.

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$4:$B$8)/($A$4:$A$8=O$3)/(INDEX($G$4:$K$8,0,MATCH($M16,$G$3:$K$3,0))="Yes")/(INDEX($C$4:$F$8,0,MATCH($N16,$C$3:$F$3,0))="Yes"),1)),"")

For the #SPILL error, see the link below.

#SPILL! error - Spill range isn't blank - Microsoft Support

This error occurs when the spill range for a spilled array formula is not blank.

support.microsoft.com

If you don't want it to spill then try array confirming it with Ctrl Shift Enter (I believe that doing so enters the formula into a single cell only instead of spilling).