Match multiple criteria and return multiple rows in excel

N

neeleshapatil

New Member
Joined
Jul 21, 2010
Messages
19
Hi

Need excel formula to return values from table array which are satisfying multiple search criteria.
following is our requirement.

<TABLE style="WIDTH: 220pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=294 border=0 x:str><COLGROUP><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 119pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" width=159 height=17>Coulmn1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=64>Coulmn2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=71></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Security A</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="Entity1 ">Entity1 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Security B</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Security A</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Security B</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity4</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Security C</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity5</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Security D</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity6</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Security A</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity7</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" height=17>Search Criteria</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>Security A,Security B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" height=17>Result</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="Entity1 ">Entity1 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity7</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Entity4</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>




We are working with Xcelsius and it doesnt support following functions

Note-
a) Array forumale, Formula is an array formula, entered using Ctrl + Shift + Enter, enclosed between braces {}
b) Row function
 
Sort by date Sort by votes
Hi Peter,

Thanks for your reply.

Firstly, what I have.

[TABLE="width: 308"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]0[/TD]
[TD]Ethnicity[/TD]
[TD]Gender[/TD]
[TD]Grade[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]known[/TD]
[TD]M[/TD]
[TD]K[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]known[/TD]
[TD]M[/TD]
[TD]K[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]M[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]M[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]F[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]F[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]M[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]known[/TD]
[TD]M[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]known[/TD]
[TD]F[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]F[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]M[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]M[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]M[/TD]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]F[/TD]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]F[/TD]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]M[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]F[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Unknown[/TD]
[TD]F[/TD]
[TD]8[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I have a table with a helper column and aspects of students profile (ie their ethnicity (2 categories known and unknown), gender (2 categories m and f), grade ((9 categories K to 8) and status ) 2 categories (1 and 0)).

I have a search box with 4 criteria - ethnicity, gender, grade and status, a record of the number of critiera selected and the matched record count

[TABLE="width: 337"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Ethnicity[/TD]
[TD]Gender[/TD]
[TD]Grade[/TD]
[TD]Status[/TD]
[TD]No of Criteria [/TD]
[/TR]
[TR]
[TD]known[/TD]
[TD]M[/TD]
[TD]k[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and lastly an output table for the results returning a list of all profiles matching the criteria. (below is the resutls of 4 criteria search)

[TABLE="width: 243"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Ethnicity[/TD]
[TD]Gender[/TD]
[TD]Grade[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]known[/TD]
[TD]M[/TD]
[TD]K[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]known[/TD]
[TD]M[/TD]
[TD]K[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]



[/TD]
[/TR]
</tbody>[/TABLE]
What I want

I wish to develop the search criteria for to extract data from the table based on any combination of the 4 criteria (ie extract data using any single criteria, or any combination of 2 criteria or any combination of 3 criteria and lastly extract matches for all 4 criteria. Similar to .neeleshapatil original posting i need a non array solution as this spreadsheet drives an xcelsius dashboard.

I have started with this formula in the helper column which performs a search and match for all 4 criteria -

=AND(D7=$D$2,E7=$E$2,F7=$F$2,G7=$G$2)+A6


and this formula for the lookup and extraction

=IF($I7>$E$4,"",INDEX(D$7:D$89,MATCH($I7,$A$7:$A$89,0),1))


I would really like to extend the forumla in the helper column so that it can filter any combination of the 4 criteria
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
For the future, have a look at my signature block for screen posting suggestions. Knowing the rows/columns/formulas involved is a great help. I think I've figured out your layout but it was a bit of a task, ;)

Just for completeness, and others viewing the thread, I've posted all the formulas I have used. That is, I've repeated yours, except for counting the criteria entered. You may need it for something else but it is not required for this task.

Formula in A7 is copied down.
Formula in J7 is copied across and down.

Excel Workbook
ABCDEFGHIJKLM
1EthnicityGenderGradeStatus
2Unknown6
3
4Count3
5
60EthnicityGenderGradeStatusEthnicityGenderGradeStatus
70knownMK11UnknownM60
80knownMK12UnknownM61
90UnknownM103UnknownF61
100UnknownM104
110UnknownF205
120UnknownF216
130UnknownM307
140knownM318
150knownF419
160UnknownF4010
170UnknownM5011
181UnknownM6012
192UnknownM6113
203UnknownF6114
213UnknownF7015
223UnknownM7116
233UnknownF8017
243UnknownF8118
Extract Data
 
Upvote 0
Hi Peter,


Thank you ever so much for this feedback.

I works !!! and was exactly what i was looking for.

Been racking my head as to how to express the and or operators

But I am still puzzled but glad it works


Could you kindly explain in particular how the D2="", E2="", F2="", G2="" in the OR statement is working.


Lastly apologies for not posting the row column details clearly.


Regrards Pops
 
Upvote 0
popop said:
Could you kindly explain in particular how the D2="", E2="", F2="", G2="" in the OR statement is working.
Click to expand...
=AND(D7=$D$2,E7=$E$2,F7=$F$2,G7=$G$2)+A6
Your existing formula (for use when there were 4 criteria used) basically said that for a row to be included in the results, each item in that row had to match the values in D2:G2.
That is, all four conditions in the AND() function had to be True.

=AND(OR(D7=D$2,D$2=""),OR(E7=E$2,E$2=""),OR(F7=F$2,F$2=""),OR(G7=G$2,G$2=""))+A6
I've just extended that idea, still requiring that all 4 columns are True, but for a column to be "True" the item in that row/column has to match the row 2 value (same condition as yours) OR the row 2 value is blank, meaning that column is automatically True since it doesn't matter what is in that column on the data row.

I'm not sure if that is any clearer, but it's the best I could do :)
 
Upvote 0
Hi Peter,

Yes, the penny had now dropped. My failure was to match the empty search string in each cell and that threw my results off. I think i was hoping that the and/or function would be doing this regardless.


Thanks very much for aiding my understanding its been ages trying to figure the solution to this.


Kind Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
517
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
893
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,222,759
Messages
6,168,052
Members
452,160
Latest member
Bekerinik

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top