Advanced - Array formula's, INDEX() formulas, and wildcard characters

G

gimpexe

New Member
Joined
Feb 18, 2014
Messages
6
Hello Everyone,

i'm using an INDEX(SMALL()) array formula to search for multiple rows in a list which match a cell's value.
It works perfectly for exact values, but when i use wildcard characters i'm not having any luck, can someone please help.

example below:
0xPyoYQ.png



The above works correctly if in the yellow box i enter the EXACT string from the data source.
However, if i enter "1ABC123" the 'Code 1 matches' section will only return row 1 of the data source.

My problem is, if i enter a string "1ABC123", i want it to show all rows which contain any part of the search string.
so if i enter a string "1ABC123", row 1 AND row 3 of the datasource will show up in the "Code 1 matches' section.

please note: all the formulas i've used are array formulas

i've tried using wildcard characters, and a few different formulas, but havnt had any luck.

if anyone knows how to do this, that would be epic.

thank you
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Images are not readable into Excel.

Would you post the formula that you have, which is not copiable from the image?
 
Upvote 0
Hello Aladin,

using the below picture for guidance, the formula's are:
8kcPwDe.png



(ARRAY FORMULA'S)
note: the -3 is needed because there is 3x empty rows above the data in the DataTable

CODE 1 MATCHES SECTION
B14 = {=INDEX(DataTable[date-time],SMALL(IF(DataTable[code1]=$C$10,ROW(DataTable[code1]),""),ROW(1:1))-3)}
B15 = {=INDEX(DataTable[date-time],SMALL(IF(DataTable[code1]=$C$10,ROW(DataTable[code1]),""),ROW(2:2))-3)}

C14 = {=INDEX(DataTable[code1],SMALL(IF(DataTable[code1]=$C$10,ROW(DataTable[code1]),""),ROW(1:1))-3)}
C15 = {=INDEX(DataTable[code1],SMALL(IF(DataTable[code1]=$C$10,ROW(DataTable[code1]),""),ROW(2:2))-3)}

D14 = {=INDEX(DataTable[code2],SMALL(IF(DataTable[code1]=$C$10,ROW(DataTable[code1]),""),ROW(1:1))-3)}
D15 = {=INDEX(DataTable[code2],SMALL(IF(DataTable[code1]=$C$10,ROW(DataTable[code1]),""),ROW(2:2))-3)}

CODE 2 MATCHES SECTION
B20 = {=INDEX(DataTable[date-time],SMALL(IF(DataTable[code2]=$C$10,ROW(DataTable[code2]),""),ROW(1:1))-3)}
B21 = {=INDEX(DataTable[date-time],SMALL(IF(DataTable[code2]=$C$10,ROW(DataTable[code2]),""),ROW(2:2))-3)}

C20 = {=INDEX(DataTable[code1],SMALL(IF(DataTable[code2]=$C$10,ROW(DataTable[code2]),""),ROW(1:1))-3)}
C21 = {=INDEX(DataTable[code1],SMALL(IF(DataTable[code2]=$C$10,ROW(DataTable[code2]),""),ROW(2:2))-3)}

D20 = {=INDEX(DataTable[code2],SMALL(IF(DataTable[code2]=$C$10,ROW(DataTable[code2]),""),ROW(1:1))-3)}
D21 = {=INDEX(DataTable[code2],SMALL(IF(DataTable[code2]=$C$10,ROW(DataTable[code2]),""),ROW(2:2))-3)}



thank you
 
Upvote 0
Try to post just DataTable. Copy the table from Excel, Paste it here, activate Go Advanced, click somewhere in the posted table, activate the icon for Table Properties (second icon on the third row), choose Full Grid as Table. Style.
 
Upvote 0
[TABLE="class: grid, width: 310"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]date-time
[/TD]
[TD]code1[/TD]
[TD]code2[/TD]
[/TR]
[TR]
[TD="align: right"]18/02/2013 17:35[/TD]
[TD]1ABC123[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2014 15:24[/TD]
[TD]1ZZZ222 Yello-Eggs[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2014 16:24[/TD]
[TD]1ABC123 Green-Eggs
[/TD]
[TD]Away[/TD]
[/TR]
</tbody>[/TABLE]

kewl =).
here you go Aladin
 
Upvote 0
gimpexe said:
[TABLE="class: grid, width: 310"]
<tbody>[TR]
[TD]date-time[/TD]
[TD]code1[/TD]
[TD]code2[/TD]
[/TR]
[TR]
[TD="align: right"]18/02/2013 17:35[/TD]
[TD]1ABC123[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2014 15:24[/TD]
[TD]1ZZZ222 Yello-Eggs[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2014 16:24[/TD]
[TD]1ABC123 Green-Eggs[/TD]
[TD]Away[/TD]
[/TR]
</tbody>[/TABLE]

kewl =).
here you go Aladin
Click to expand...

B14, control+shift+enter (CSE), not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(DataTable[date-time],SMALL(IF(DataTable[code2]=$C$10,
  ROW(DataTable[code2])-ROW(INDEX(DataTable[code2],1,1))+1),
  ROWS(B$14:B14))),"")
C14, cse and copy down:
Rich (BB code): 
=IFERROR(INDEX(DataTable[code1],SMALL(IF(DataTable[code2]=$C$10,
  ROW(DataTable[code2])-ROW(INDEX(DataTable[code2],1,1))+1),
  ROWS(C$14:C14))),"")
D15, just enter and copy down:
Rich (BB code): 
=IF($B14="","",$C$10)
 
Upvote 0
thank you for your help Aladin.
your formula is close to what I'm trying to achieve.

the specific scenario i need to solve it below:

iIha40H.png



if i use a lookup value of 1ABC123, i want all rows from that datatable, which has a string in the code1 column which contains the lookup value string.
in the picture above, i want both "1ABC123" and "1ABC123 Green-Eggs" to be found, and to show up in the Code 1 matches section.


Thank you for the formula's:
(these have all been entered with cntrl+shift+enter)
F12
Code: 
=IFERROR(INDEX(DataTable[date-time],SMALL(IF(DataTable[code1]=$C$8,ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),ROWS($F$12:F12))),"")

F13
Code: 
=IFERROR(INDEX(DataTable[date-time],SMALL(IF(DataTable[code1]=$C$8,ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),ROWS($F$12:F13))),"")

G12
Code: 
=IFERROR(INDEX(DataTable[code1],SMALL(IF(DataTable[code1]=$C$8,ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),ROWS($G$12:G12))),"")

G13
Code: 
=IFERROR(INDEX(DataTable[code1],SMALL(IF(DataTable[code1]=$C$8,ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),ROWS($G$12:G13))),"")

H12
Code: 
=IFERROR(INDEX(DataTable[code2],SMALL(IF(DataTable[code1]=$C$8,ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),ROWS($H$12:H12))),"")

H13
Code: 
=IFERROR(INDEX(DataTable[code2],SMALL(IF(DataTable[code1]=$C$8,ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),ROWS($H$12:H13))),"")
 
Last edited:
Upvote 0
I'm sorry with your images you create confusion. Try to be concise... Your code 1 matches and code 2 matches are quite a mystery to me. What is needed is a clear formulation of the condition (look up value). It was first "away" seemingly, now it is '1ABC123". And, presumably, you want to obtain:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]date-time[/TD]
[TD="width: 146"]code1[/TD]
[TD="width: 105"]code2[/TD]
[/TR]
[TR]
[TD]18/02/2013 17:35
[/TD]
[TD]1ABC123[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD]31/01/2014 16:24[/TD]
[TD]1ABC123 Green-Eggs
[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Right?
 
Upvote 0
correct.

apologies Aladin, i took two screenshots (1x where i had a lookup value of 'away' and 1x where i had a lookup value of '1abc123') i posted the wrong one into the forum post.

those are the rows i want the formula to find, yes.
 
Upvote 0
gimpexe said:
correct.

apologies Aladin, i took two screenshots (1x where i had a lookup value of 'away' and 1x where i had a lookup value of '1abc123') i posted the wrong one into the forum post.

those are the rows i want the formula to find, yes.
Click to expand...

Thanks...

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]date-time[/TD]
[TD="width: 146"]code1[/TD]
[TD="width: 105"]code2[/TD]
[/TR]
[TR]
[TD="width: 130"]18/02/2013 17:35
[/TD]
[TD="width: 146"]1ABC123[/TD]
[TD="width: 105"]Home[/TD]
[/TR]
[TR]
[TD="width: 130"]31/01/2014 15:24[/TD]
[TD="width: 146"]1ZZZ222 Yello-Eggs[/TD]
[TD="width: 105"]Away[/TD]
[/TR]
[TR]
[TD="width: 130"]31/01/2014 16:24[/TD]
[TD="width: 146"]1ABC123 Green-Eggs[/TD]
[TD="width: 105"]Away[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lookup Value[/TD]
[TD]1ABC123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 130"]date-time[/TD]
[TD="width: 146"]code1[/TD]
[TD="width: 105"]code2[/TD]
[/TR]
[TR]
[TD]18/02/2013 17:35[/TD]
[TD]1ABC123[/TD]
[TD]Home[/TD]
[/TR]
[TR]
[TD]31/01/2014 16:24[/TD]
[TD]1ABC123 Green-Eggs[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

B12, control+shift+enter (cse), not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(DataTable[date-time],
  SMALL(IF(ISNUMBER(SEARCH($C$8,DataTable[code1])),
  ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),
  ROWS(B$12:B12))),"")

C12, cse and copy down:
Rich (BB code): 
=IFERROR(INDEX(DataTable[code1],
  SMALL(IF(ISNUMBER(SEARCH($C$8,DataTable[code1])),
  ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),
  ROWS(C$12:C12))),"")

D12, cse and copy down:
Rich (BB code): 
=IFERROR(INDEX(DataTable[code2],
  SMALL(IF(ISNUMBER(SEARCH($C$8,DataTable[code1])),
  ROW(DataTable[code1])-ROW(INDEX(DataTable[code1],1,1))+1),
  ROWS(D$12:D12))),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Use of wildcards inside MATCH
Replies
2
Views
169
JesusPM
J
B
  • Solved
VLOOKUP issue with wildcard parameter for partial matches in number/letter combinations
2
Replies
10
Views
851
BrooksProctor
B
U
  • Question Question
Dynamic Macro for INDEX/MATCH Formula
Replies
1
Views
365
StephenCrump
S
R
  • Question Question
Multi-Criteria Index-Match with Wildcard
Replies
3
Views
549
Phuoc
P
Dugzilla
  • Solved
Index Match not matching
Replies
8
Views
666
Dugzilla
Dugzilla

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

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