Index/Match with Multiple Criteria

[chethead]

Hello,

I'm really struggling with this one. I believe I need to use an index match lookup combo. The goal is to pull information from sheet 1 into other "forms" (pre-populated sheets) in a workbook. I would like to return all information from column C that has the correct text in columns A, B and D. I'm only looking to meet these three criteria: base bid, fire alarm and quoted:

[TABLE="width: 350"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Base Bid
[/TD]
[TD]Nurse Call[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="width: 117"]Pull Cord[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Quoted[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Base Bid[/TD]
[TD]Nurse Call[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="width: 117"]Dome Light[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Quoted[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Base Bid[/TD]
[TD]Fire Alarm[/TD]
[TD]Smoke Det[/TD]
[TD]Quoted[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Base Bid[/TD]
[TD]Fire Alarm[/TD]
[TD]Heat Det[/TD]
[TD]Quoted[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Base Bid[/TD]
[TD]Fire Alarm[/TD]
[TD]Pull Station[/TD]
[TD]Quoted[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Base Bid[/TD]
[TD]Security[/TD]
[TD]Door Contact[/TD]
[TD]Quoted[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 I have labeled "Fire Alarm". So on sheet 2:

A1 would read "Smoke Det"
A2 would read "Heat Det"
A3 would read "Pull Station"

I would be very grateful for an answer!

Thank you

 

[AhoyNC]

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag down as needed.

Excel Workbook
	A
1	Smoke Det
2	Heat Det
3	Pull Station
4
Sheet2

Excel Workbook
	A	B	C	D
1	Base Bid	Nurse Call	Pull Cord	Quoted
2	Base Bid	Nurse Call	Dome Light	Quoted
3	Base Bid	Fire Alarm	Smoke Det	Quoted
4	Base Bid	Fire Alarm	Heat Det	Quoted
5	Base Bid	Fire Alarm	Pull Station	Quoted
6	Base Bid	Security	Door Contact	Quoted
Sheet1

 

[chethead]

Thank you very much! I've got it working pretty well. I'm trying to add one more argument. Column D on sheet 1 is either "Normal", "Quoted" or "Subcontractor". I would like to return column C on sheet 2 id column D is either "Quoted" or "Subcontractor", but not "Normal". I tried this:

{=IFERROR(INDEX(Estimate!$C$1:$C$2500,SMALL(IF(Estimate!$B$1:$B$2500<>"",IF(Estimate!$A$1:$A$2500=" || BASE BID",IF(OR(Estimate!$O$1:$O$2500="Quoted",Estimate!$O$1:$O$2500="Subcontractor"),IF(Estimate!$B$1:$B$2500=" || 2020 - FIRE ALARM SYSTEM",ROW(Estimate!$B$1:$B$2500)-ROW(Estimate!$B$1)+1)))),ROWS($A$1:A1))),"")}

I'm not familiar with the Small function so maybe that is where I am screwing things up

 

[AhoyNC]

The issue is with the OR function. It's only going to return TRUE or FALSE, but you need an array of TRUEs and FALSEs.
Try changing the formula to: (enter with CTRL-SHIFT-ENTER).

[TABLE="width: 1790"]
<colgroup><col width="1790"></colgroup><tbody>[TR]
   [TD="width: 1790"]=IFERROR(INDEX(Estimate!$C$1:$C$2500,SMALL(IF(Estimate!$B$1:$B$2500<>"",IF(Estimate!$A$1:$A$2500="   || BASE   BID",IF([COLOR=#ff0000](Estimate!$O$1:$O$2500="Quoted")+(Estimate!$O$1:$O$2500="Subcontractor")[/COLOR],IF(Estimate!$B$1:$B$2500="   || 2020 - FIRE ALARM   SYSTEM",ROW(Estimate!$B$1:$B$2500)-ROW(Estimate!$B$1)+1)))),ROWS($A$1:A1))),"")
[/TD]
 [/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

ISNUMBER/MATCH would be more flexible then + here...

{=IFERROR(INDEX(Estimate!$C$1:$C$2500,SMALL(IF(Estimate!$B$1:$B$2500<>"",IF(Estimate!$A$1:$A$2500=" || BASE BID",IF(ISNUMBER(MATCH(Estimate!$O$1:$O$2500,{"Quoted","Subcontractor"},0)),IF(Estimate!$B$1:$B$2500=" || 2020 - FIRE ALARM SYSTEM",ROW(Estimate!$B$1:$B$2500)-ROW(Estimate!$B$1)+1)))),ROWS($A$1:A1))),"")}

 

[chethead]

Thank you to both of you! I appreciate the help. And thanks for exposing me to some new functions. Looking forward to tearing this apart to understand what I am doing