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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
607
Peter_SSs
Peter_SSs
N
  • Solved
Amend Formula for Manipulating Names
Replies
2
Views
287
NorthbyNorthwest
N
H
  • Question Question
Help with INDIRECT function please
Replies
5
Views
463
Joe4
Joe4
T
  • Solved
Search for values within a range and return where they've been found
Replies
4
Views
392
tigerzen
T
T
  • Question Question
Reconfiguring data
2
Replies
13
Views
692
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,591
Messages
6,179,768
Members
452,940
Latest member
rootytrip

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