Returning Multiple Values via Index Match that are < cell and = to another cell

Good Evening,

I'm struggling to create a formula that creates a list of employees who fall below a certain salary.

I have an excel file with thousands of employees - of varying jobs and roles. I've been trying to find a way to identify all of those employees of a certain job (selected in cell B2 (a text value)) who fall below the minimum market salary for that respective job (cell B6 (numeric value)). Therefore, if you select a different job in B2, a different minimum wage appears in B6.

Say you select "waitress" in cell B2. Cell B6 now correctly shows the minimum market wage for "waitress." However, I have not been able to figure out how to identify all of the employees who fall below that specified range. I'm hoping to be able to create a list of employees that automatically updates as you select different jobs in cell B2...

I've been trying to index-match the information and I've also been trying index with small & countif to create a "self populating" list. However, I seem to be having an error in terms of the "< than" piece.

This is as far as I have gotten - but it just doesn't seem to be working...

=INDEX('Employee Data'!$B$2:$B$4500,SMALL(IF(COUNTIF('Employee Data'!$D$2:$D$4500,"<"&$B$6)*COUNTIF('Employee Data'!$G$2:$G$4500,$B$2),ROW('Employee Data'!D3:D4501)-MIN(ROW('Employee Data'!D3:D4501))+1),ROW('Employee Data'!A2)),COLUMN('Employee Data'!A2))

B2:B4500 = employee names
D2:D4500 = employee annual salary
G2:G4500 = job titles

Let me know if you have any questions. I hope this makes sense

Posting a workbook would eliminate some of the guesswork here.

Make sure there is enough data for us to get it right.
Remember to desensitize the sample data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Originally Posted by JohnTopley

in b13

=IFERROR(INDEX('Employee Data'!$A$1:$A$500,SMALL(IF(('Employee Data'!$B$1:$B$500<=Work!$B$6)*('Employee Data'!$D$1:$D$500=Work!$B$2),ROW('Employee Data'!$A$1:$A$500),""),ROW(1:1))),"")

In D13

=IFERROR(INDEX('Employee Data'!$B$1:$B$500,SMALL(IF(('Employee Data'!$B$1:$B$500<=Work!$B$6)*('Employee Data'!$D$1:$D$500=Work!$B$2),ROW('Employee Data'!$A$1:$A$500),""),ROW(1:1))),"")

Better (read: more robust) to use the ROWS( ) function instead of the ROW( ) function. If you insert new rows before row 1 the formula will return incorrect results. The ROWS( ) function will account for this.

=IFERROR(INDEX('Employee Data'!$A$1:$A$500,SMALL(IF(('Employee Data'!$B$1:$B$500<=Work!$B$6)*('Employee Data'!$D$1:$D$500=Work!$B$2),ROW('Employee Data'!$A$1:$A$500)),ROWS(B$13:B13))),"")

=IFERROR(INDEX('Employee Data'!$B$1:$B$500,SMALL(IF(('Employee Data'!$B$1:$B$500<=Work!$B$6)*('Employee Data'!$D$1:$D$500=Work!$B$2),ROW('Employee Data'!$A$1:$A$500)),ROWS(D$13:D13))),"")

Both formulas still array entered!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.