If with fuzzy lookup?

M

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,302
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I know with countif you can have a fuzzy lookup i.e

=COUNTIF($A$1:$A$6,"*"&F1&"*") = 3
F1 = Harry

A1:A6

HARRY
JAMES
BEN AND HARRY
BEN
MARK AND HARRY
MARK

Is there a way i could add an OR criteria for the criteria range in Countif

i.e =COUNTIF($A$1:$A$6,OR("*"&F1&"*","*"&F2&"*") )

Another quick question

How can i look at the range A1:A6 and have it equal to a fuzzy lookup and then give me that row number where it matches?

e.g

A1:A6

HARRY
JAMES
BEN AND HARRY
BEN
MARK AND HARRY
ME AND JAMES

F1 = HARRY

LOOK THROUGH THE RANGE A1:A6, AND TELL ME THE ROW NUMBERS WHERE I HAVE HARRY IN THERE, IN MY CASE 1,3 AND 5 (i can then use the small function and rows and index to give the data the data in order

HARRY
BEN AND HARRY
MARK AND HARRY

IF POSSIBLE CAN I HAVE 2 CRITERIAS SUCH AS

LOOK THROUGH THE RANGE A1:A6, AND TELL ME THE ROW NUMBERS WHERE I HAVE JAMES AND BEN

IN MY CASE 2, 3, 4 AND 6

JAMES
BEN AND HARRY
BEN
ME AND JAMES
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
mahmed1 said:
I know with countif you can have a fuzzy lookup i.e

=COUNTIF($A$1:$A$6,"*"&F1&"*") = 3
F1 = Harry

A1:A6

HARRY
JAMES
BEN AND HARRY
BEN
MARK AND HARRY
MARK

Is there a way i could add an OR criteria for the criteria range in Countif

i.e =COUNTIF($A$1:$A$6,OR("*"&F1&"*","*"&F2&"*") )

Another quick question

How can i look at the range A1:A6 and have it equal to a fuzzy lookup and then give me that row number where it matches?

e.g

A1:A6

HARRY
JAMES
BEN AND HARRY
BEN
MARK AND HARRY
ME AND JAMES

F1 = HARRY

LOOK THROUGH THE RANGE A1:A6, AND TELL ME THE ROW NUMBERS WHERE I HAVE HARRY IN THERE, IN MY CASE 1,3 AND 5 (i can then use the small function and rows and index to give the data the data in order

HARRY
BEN AND HARRY
MARK AND HARRY

IF POSSIBLE CAN I HAVE 2 CRITERIAS SUCH AS

LOOK THROUGH THE RANGE A1:A6, AND TELL ME THE ROW NUMBERS WHERE I HAVE JAMES AND BEN

IN MY CASE 2, 3, 4 AND 6

JAMES
BEN AND HARRY
BEN
ME AND JAMES
Click to expand...

Something like this but the If part is not working but this is what i am after

=INDEX($A$1:$A$6,SMALL(IF($A$1:$A$6=OR("*"&F1&"*","*"&F2&"*"),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($A$1:A1)))
 
Upvote 0
mahmed1 said:
Something like this but the If part is not working but this is what i am after

=INDEX($A$1:$A$6,SMALL(IF($A$1:$A$6=OR("*"&F1&"*","*"&F2&"*"),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($A$1:A1)))
Click to expand...


bump pleaseee
 
Upvote 0
Try this,

=INDEX($A$1:$A$6,SMALL(IF(ISNUMBER(SEARCH("#####",SUBSTITUTE(PROPER($A$1:$A$6),TRANSPOSE(PROPER($F$1:$F$2)),"#####"))),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS(B$1:B1)))

Confirmed with CTRL+SHIFT+ENTER.

Copy down...

EDIT: Or

=INDEX($A$1:$A$6,SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE($F$1:$F$2),$A$1:$A$6)),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS(B$1:B1)))

Confirmed with CTRL+SHIFT+ENTER.
 
Last edited:
Upvote 0
I think the OP is asking for native row numbers. Adjusting for that, we get something like Avarakhan's second option...

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=385><COLGROUP><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5461" width=154><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3669" width=103><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 115pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=154>HARRY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=103>James</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>JAMES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Ben</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>BEN AND HARRY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Count</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>BEN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>MARK AND HARRY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Native Row(s)</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>ME AND JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR></TBODY></TABLE>

D4, just enter:
Code: 
=SUMPRODUCT(COUNTIF(A1:A6,"*"&D1:D2&"*"))

D6, control+shift+enter, not just enter, and copy down:
Code: 
=IF(ROWS($D$6:D6)<=$D$4,
   SMALL(IF(ISNUMBER(SEARCH(TRANSPOSE($D$1:$D$2),$A$1:$A$6)),
    ROW($A$1:$A$6)),ROWS($D$6:D6)),"")
 
Upvote 0
Hi Aladdin or Haseeb, can you please advise why you used the transpose function, do you need to have the transpose?

Just trying to understand this part

Thanks
 
Upvote 0
mahmed1 said:
Hi Aladdin or Haseeb, can you please advise why you used the transpose function, do you need to have the transpose?

Just trying to understand this part

Thanks
Click to expand...


Bump

Thank you
 
Upvote 0
mahmed1 said:
Bump

Thank you
Click to expand...

please bit more patient...

If you change D1:D2 to, say D1:E1, you can drop TRANSPOSE.
TRANSPOSE(D1:D2) or D1:E1 allows SEARCH to create 2-column result, as it should be: One column for James, another for Ben.

Hope this helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
HELP---- Count Cells in a row based on Month and Unique cell contents.
2
Replies
16
Views
220
Peter_SSs
Peter_SSs
H
  • Question
Help with INDIRECT function please
Replies
5
Views
334
Joe4
Joe4
T
  • Solved
Search for values within a range and return where they've been found
Replies
4
Views
273
tigerzen
T
T
  • Question
Reconfiguring data
2
Replies
13
Views
490
Peter_SSs
Peter_SSs
Johnny Thunder
  • Question
VBA Help - Loop Thru List and Copy Value to a Range and repeat to next list value
Replies
2
Views
227
Mario_R
M

Forum statistics

Threads
1,214,544
Messages
6,120,126
Members
448,947
Latest member
test111

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