# If with fuzzy lookup?

#### mahmed1

##### Well-known Member
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### mahmed1

##### Well-known Member
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

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)))

#### mahmed1

##### Well-known Member
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)))

#### Haseeb Avarakkan

##### Well-known Member
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:

##### MrExcel MVP
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)),"")``````

#### mahmed1

##### Well-known Member
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

#### mahmed1

##### Well-known Member
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

Bump

Thank you

##### MrExcel MVP
Bump

Thank you

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.

Replies
4
Views
262
Replies
16
Views
99
Replies
3
Views
66
Replies
4
Views
113
Replies
4
Views
55

1,191,034
Messages
5,984,261
Members
439,881
Latest member
Amitoj95

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

### Which adblocker are you using?

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

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