Triple criteria lookup

sirmixalot

New Member
Joined
Jan 15, 2016
Messages
7
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
1597266988585.png



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

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
 

Attachments

  • 1597266664918.png
    1597266664918.png
    124.2 KB · Views: 9
  • 1597266692151.png
    1597266692151.png
    52.6 KB · Views: 9

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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).
 
Upvote 0
PlansLocations
#GBbwbmvmocAugustaLoganvilleDestinWestinFrance
0bm-gq3aNoYesNoNoYesYesYesYesNo
1gm-gq3bNoYesNoNoYesYesYesNoNo
2bm-gq3cNoYesNoNoYesYesYesNoNo
3bm-gq4aNoYesNoNoYesYesYesYesNo
4san-fb-123tNoYesNoNoYesYesYesYesYes


000011112222
locationplanattached_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]
Augustaoc
Loganvilleoc
Destinoc
Westinoc
Franceoc
Augustavm
Loganvillevm
Destinvm
Westinvm
Francevm
Augustabm
Loganvillebm
Destinbm
Westinbm
Francebm



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
 
Upvote 0
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.

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).
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top