How to get ID numbers based on multiple criteria in excel?

Question

I am dealing with the following situation in Excel. I have a table as shown below (range is arbitrary).

I want to know if there is any function that I can use to get the ID's that :

• worked more than 8 hours, and
• acceptance rate for them is greater than 85, and
• satisfaction of customer rate is greater than 4?

I know I can get this simply by adding filters but I was wondering what is the best approach?

Should I create a customized table and add all my criteria and then do a VLOOKUP?

Any help will be much appreciated.

Thanks a lot

Answer 1

Without Table

1. Define this four Names: (ctrl+F3)

Below:

• (I)

Name: ID

Refers to: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$999))

• (II)

Name: Hours

Refers to: =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D$2:$D$999))

• (III)

Name: SatisfactionRate

Refers to: =OFFSET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$E$2:$E$999))

• (IV)

Name: AcceptanceRate

Refers to: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$999))

2. Write this array formula

Case1:

• select the results container range.
• Press F2.
• Press Ctrl+Shift+Enter after
  entering this formula.

=INDEX(ID,SMALL(IF(AND(Hours>8,SatisfactionRate>4,AcceptanceRate>85),ROW(ID)-ROW($A$1)),ROW($A:$A)))

Case2:

• Select the upper cell you want to return first result (vertically results).
• Press F2.
• Press Ctrl+Shift+Enter after
  entering this formula.
• Extend the formula in below cells you want involving results.

=INDEX(ID,SMALL(IF(AND(Hours>8,SatisfactionRate>4,AcceptanceRate>85),ROW(ID)-ROW($A$1)),ROW(A1)))

---

With Table

1. Define range container Table.

Insert-> Table

2. Write above array form formula for table range as below

Replace Name ranges with these in array formulas.

• ID -> Table1[ID]
• Hours -> Table1[Hours]
• SatisfactionRate -> Table1[SatisfactionRate]
• AcceptanceRate -> Table1[AcceptanceRate]