Small Function with Multiple Criteria

**Peelah** · 02-10-2022, 01:56 AM

Hi,

I'm trying to combine the small function with the OR function to return all values which match certain criteria.

Summary Tab: I want to automate this tab by sourcing from the Data Sheet, matching the criteria listed in the grey text in row 1 ("part-time" or "temp" status in the data tab)

I tried this formula, but it's not working:

=IFERROR(INDEX(Data!$A$3:$C$265,SMALL(IF(Data!$B$3:$B$265=$A$1)+(Data!$B$3:$B$265=$B$1)),ROW(Data!$B$3:$B$265)-ROW(Data!$2:$2)),ROWS(A$3:A3)),MATCH(A$2,Data!$2:$2,0))),""))

Any ideas would be greatly appreciated!

Thanks!

**menem** · 02-10-2022, 02:50 AM

If you don't mind to use helper.

Helper : E3
=AGGREGATE(15,6,(ROW(Data!$B$2:$B$6)-ROW(Data!$B$1))/(1/(OR(Data!$B$2:$B$6=$A$1,Data!$B$2:$B$6=$B$1))),ROWS(E$2:E2))
copy down

Answer area.
A3
=IFERROR(INDEX(Data!A$2:A$6,$E3),"")
copy down and across.

Regards.

**wk9128** · 02-10-2022, 03:09 AM

worksheet or tab name : Summary

Cell A3 formula , Drag down and across

Formula:

=IFERROR(INDEX(Data!$A:$C,AGGREGATE(15,6,ROW($3:$265)/(Data!$B$3:$B$265=$A$1),ROW(1:1)),MATCH(A$2,Data!$A$1:$C$1,)),"")

OR

Formula:

=IFERROR(INDEX(Data!$A:$C,SMALL(IF(Data!$B$3:$B$265=$A$1,ROW($3:$265)),ROW(1:1)),MATCH(A$2,Data!$A$1:$C$1,)),"")

**JohnTopley** · 02-10-2022, 03:50 AM

Try

Formula:

=IFERROR(INDEX(Data!A$2:A$100,AGGREGATE(15,6,ROW($A$1:$A$100)/((Data!$B$2:$B$100=$A$1)+(Data!$B$2:$B$100=$B$1)),ROWS($1:1))),"")

**Peelah** · 02-10-2022, 12:23 PM

Thanks, this one worked!

**JohnTopley** · 02-10-2022, 12:40 PM

When you reply, please let us know to whom you are responding if you have multiple replies.

If your query has been answered please mark as SOLVED: See "thread Tools" at top of thread.

**Peelah** · 02-10-2022, 12:21 PM

Thanks, this only seems to capture the "Part-time" status, and not "Temp" too

**CARACALLA** · 02-10-2022, 12:35 PM

Another

A3 Summary

=IFERROR(INDEX(Data!A$2:A$100,AGGREGATE(15,6,ROW(Data!A$2:A$100)-ROW(Summary!A$2)+1/ISNUMBER(MATCH(Data!$B$2:$B$100,Summary!$A$1:$B$1,0)),ROWS($A$2:A2))),"")

Copy across and down

Small Function with Multiple Criteria - OR function

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Small Function with Multiple Criteria - OR function

Re: Small Function with Multiple Criteria - OR function

Re: Small Function with Multiple Criteria - OR function

Re: Small Function with Multiple Criteria - OR function

Re: Small Function with Multiple Criteria - OR function

Re: Small Function with Multiple Criteria - OR function

Re: Small Function with Multiple Criteria - OR function

Re: Small Function with Multiple Criteria - OR function

Thread Information

Users Browsing this Thread

Similar Threads

Small function based on one criteria.

Need help with the SMALL function using non continuous number and criteria

[SOLVED] Count if, small and row function if only 1 criteria

Small Function with Criteria

Excel 2007 : Small function help for 2 criteria

[SOLVED] Use criteria for values in range for small function

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions