[Function] Return multiple values by multiple criteria, AND criteria is a cell which may contains WILDCARD (*)

Xanthux

New Member
Joined
Mar 1, 2014
Messages
24
Hi everyone, I am trying to do a multiple criteria lookup with multiple criteria.

Indeed, I think the tutorial in the following link is useful:
Match two criteria and return multiple rows in excel | Get Digital Help - Microsoft Excel resource

(Using the sample from the above link) BUT sometimes in my case, the Cell G3 and G4 may contain a WILDCARD (*) character.
Once I change it to wildcard character, the array formula suggested by the above link does not work anymore.

Anyone knows how to get this work?

Great great thanks in advance.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Hi everyone, I am trying to do a multiple criteria lookup with multiple criteria.

Indeed, I think the tutorial in the following link is useful:
Match two criteria and return multiple rows in excel | Get Digital Help - Microsoft Excel resource

(Using the sample from the above link) BUT sometimes in my case, the Cell G3 and G4 may contain a WILDCARD (*) character.
Once I change it to wildcard character, the array formula suggested by the above link does not work anymore.

Anyone knows how to get this work?

Great great thanks in advance.

May I suggest that you re-type the sample yourself from that link into Excel and copy-and-paste the re-typed bit here with necessary modification?
 

Xanthux

New Member
Joined
Mar 1, 2014
Messages
24
May I suggest that you re-type the sample yourself from that link into Excel and copy-and-paste the re-typed bit here with necessary modification?


ABCEFG
1






2SecurityDatePriceSearch
3SecurityA1 1 2009$46.10Security:SecurityB
4SecurityB1 2 2009$1.40Date:1 2 2009
5SecurityC1 3 2009$90.70
6SecurityD1 4 2009$37.70Search results
7SecurityA1 1 2009$52.90
8SecurityB1 2 2009$70.60SecurityDatePrice
9SecurityC1 3 2009$53.90SecurityB1 2 2009$1.40
10SecurityD1 4 2009$63.10SecurityB1 2 2009$70.60
11SecurityA1 5 2009$4.50#NUM!#NUM!#NUM!
12SecurityB1 6 2009$75.80
13SecurityC1 7 2009$76.20
14SecurityD1 5 2009$64.00
15SecurityA1 6 2009$17.90
16SecurityB1 7 2009$61.70
17SecurityC1 5 2009$58.30
18SecurityD1 6 2009$23.60
19SecurityA1 7 2009$43.20

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

The link suggested this formula in cell F9:
=INDEX(tbl, SMALL(IF(COUNTIFS($G$3, $B$3:$B$19, $G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))

and tbl refers to B2:D19
----------
However in my case, sometimes Cell G3 and G4 will be a wildcard character (*), which means I want all Security or Dates respectively. But changing Cell G3 and G4 to * will cause the array formula to fail, and I have no idea to get around this.

Thank you Aladin again.

 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
F9, control+shift+enter, not just enter, copy across, and down:
Rich (BB code):
=IFERROR(INDEX(B$3:B$19,SMALL(IF($B$3:$B$19=IF($G$3="*",$B$3:$B$19,$G$3),
  IF($C$3:$C$19=IF($G$4="*",$C$3:$C$19,$G$4),ROW($D$3:$D$19)-ROW($D$3)+1)),
  ROWS(F$9:F9))),"")

Note. Such sites do more harm than help. For example, the usage ROW(A1) and COLUMN(A1) are potentially harmful.
 

azumi

Well-known Member
Joined
Jun 4, 2013
Messages
555

ADVERTISEMENT

COnsider your tables in rnges A2:C19 (with Column Header):

and your criteria input in Cell G3 and G4

Changes your formula to this:

=IFERROR(INDEX(A$3:A$19,SMALL(IF(ISNUMBER(SEARCH($G$4,$A$3:$A$19))*ISNUMBER(SEARCH($G$5,$B$3:$B$19)),ROW($A$3:$A$19)-MIN(ROW($A$3:$A$19))+1),ROWS($E$9:E9))),"")

and when you need all results, just leave it blank in G3 and G4, it will generate all results

or see this

https://www.dropbox.com/s/9jrpfq0ou078fmi/Search.xlsx

Hope it helpls
Azumi
 

Xanthux

New Member
Joined
Mar 1, 2014
Messages
24
Thanks to both Aladin and Azumi, both of your solution works.
However, Aladin's solution suits my case more as the criteria in my case are in a drop-down list.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,411
Messages
5,528,621
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top