[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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,170
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,170
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
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,096,454
Messages
5,450,554
Members
405,616
Latest member
awiltz

This Week's Hot Topics

Top