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:

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

	A	B	C	D
1	Base Bid	Nurse Call	Pull Cord <tbody> </tbody>	Quoted
2	Base Bid	Nurse Call	Dome Light <tbody> </tbody>	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

<tbody>
</tbody>

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