Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

**vikrampnz** · 08-20-2018, 07:29 AM

Hello

I have a Register workbook (Please see attached sample file) showing cheques given to various people. In the same workbook, I have a "Summary" sheet where it shows me the snapshot of the database.

In the Summary sheet, I have a dynamic list showing "Cheques Due This Week" under which I am currently picking the relevant rows from the Register based on current Week Number.

In the Register worksheet, in column M, I have Cheque status i.e. Active or non-Active.

1) I am trying figure out a way to display records that meet both the criteria i.e. Current Week Number and "Active" status in column M of worksheet "Register". If the status is "Non-Active" , then that record should not be displayed on the summary Page under "Cheques Due This Week".

2) Is there a way to show "Blank cells" where there is no value to show instead of the "#NUM!"

Can someone please help ! I have attached my sample sheet

Thanks in Advance

V

**Special-K** · 08-20-2018, 07:31 AM

Put the formula in an IFERROR

=IFERROR(formula,"")

**vikrampnz** · 08-20-2018, 07:34 AM

Thanks special-K, it solved my second question ! Works well. Can someone help me with the first question too, please? Thanks

**Special-K** · 08-20-2018, 07:44 AM

These formulas are shorter and include your "Active" check

in B16
=IFERROR(INDEX(Register!$D$3:$D$10000,SMALL(IF((Register!$C$3:$C$10000=$C$13)*(Register!M$3:M$10000="Active"),ROW($A$3:$A$10000)),ROW(A1))-(ROW(A$3)-1),COLUMN()-2,"")
Array formula, use Ctrl-Shift-Enter

copy across and down as far as F32

Then
in G16
=IFERROR(INDEX(Register!$D$3:$D$10000,SMALL(IF((Register!$C$3:$C$10000=$C$13)*(Register!M$3:M$10000="Active"),ROW($A$3:$A$10000)),ROW(A1))-(ROW(A$3)-1),COLUMN()-3,"")

copy down as far as H32
Array formula, use Ctrl-Shift-Enter

**vikrampnz** · 08-20-2018, 08:04 AM

Hi Special-K

Thanks for your prompt response. I copy pasted the formula as instructed by you, but its only showing the Date and Cheque No. value in column B & C. All other columns went completely blank.

Also, it doesnt seem to exclude a record if its status in Register is "Non-Active". It shows all the records having a cheque date falling in current week.

Thanks

**Special-K** · 08-20-2018, 08:34 AM

Oops! I think it should be this

=IFERROR(INDEX(Register!$D$3:$H$10000,SMALL(IF((Register!$C$3:$C$10000=$C$13)*(Register!$M$3:$M$10000="Active"),ROW($A$3:$A$10000)),ROW(A1))-(ROW(A$3)-1),COLUMN()-2),"")
Array formula, use Ctrl-Shift-Enter

copy across and down as far as F32

Then
in G16
=IFERROR(INDEX(Register!$J$3:$K$10000,SMALL(IF((Register!$C$3:$C$10000=$C$13)*(Register!$M$3:$M$10000="Active"),ROW($A$3:$A$10000)),ROW(A1))-(ROW(A$3)-1),COLUMN()+3),"")

copy down as far as H32
Array formula, use Ctrl-Shift-Enter

**Glenn Kennedy** · 08-20-2018, 08:41 AM

Vikrampnz... your profile says you have Excel 2003, yet your file is an .xlsx. Please amend your profile to shw what you ARE using and state the EARLIEST Excel version that this solution needs to work with.

**Special-K** · 08-20-2018, 08:42 AM

My second formula still doesnt work, checking it now...

**Special-K** · 08-20-2018, 08:45 AM

Second formula should be

=IFERROR(INDEX(Register!$J$3:$K$10000,SMALL(IF((Register!$C$3:$C$10000=$C$13)*(Register!$M$3:$M$10000="Active"),ROW($A$3:$A$10000)),ROW(E1))-(ROW(E$3)-1),COLUMN()-6),"")
Array formula, use Ctrl-Shift-Enter

**Glenn Kennedy** · 08-20-2018, 10:25 AM

You have not answered my Q about your Excel version.

If, by some chance, you are actually using Excel 2010 or later, you can also use a neat non-array formul, like this one in B16, copied over to F16 and down, with a variant in G16 (to take account of the fact that you are omitting one column from the raw data), copied across and down:

=IFERROR(INDEX(Register!D:D,AGGREGATE(15,6,ROW(Register!$C$3:$C$801)/((Register!$C$3:$C$801=$C$13)*(Register!$M$3:$M$801="Active")), ROWS($A$1:A1))),"")

see sheet.

If you DO NOT have Excel 2010, or later, just ignore this post.

**vikrampnz** · 08-20-2018, 11:43 AM

Thanks a lot Special-K. Your formula works like a dream !! Cheers!

Also, I am updating my profile for the version of Excel.

Thanks guys!

Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

LinkBack

Thread Tools

Rate This Thread

Display

Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Re: Return Multiple Rows (Values) for Two Search Criteria using Index/Match Function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index/Match with Multiple Criteria and Multiple return values

[SOLVED] Return Multiple values from a column with index and match, and search criteria

INDEX / MATCH formula to return multiple values - linked to several criteria (buttons)

Search for Multiple Criteria and Return all Rows that Match.

[SOLVED] How to return multiple values that match a single search criteria?

SOS:Return multiple values against multi criteria match and index function

[SOLVED] INDEX-MATCH function return '0' if search criteria not found

Bookmarks

Bookmarks

Posting Permissions