[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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
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.

 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

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