Finding exact numbers

[Jimboexcel]

Hi!

I'm new to excel. Using the following formula, when searching for single-digit numbers, double-digit number are also found and reverse, e.g. 62 and 26.

=IF(ISBLANK($F$1),0,ISNUMBER(SEARCH($F$1,$A1&$B1&$C1&$D1)))

How do I modify this formula to search for exact numbers

Thanks

Jimbo

 

[AhoyNC]

Try:
=IF(ISBLANK($F$1),0,ISNUMBER(MATCH($F$1,$A$1:$D$1,0)))

 

[Jimboexcel]

Thanks, but the above formula does not apply formatting and highlight the rows when the number is entered.

 

[Peter_SSs]

Welcome to the MrExcel board!

Thanks, but the above formula does not apply formatting and highlight the rows when the number is entered.

That could well be because neither formatting nor highlighting were mentioned in your first post.

Remember that we cannot see your sheet or know what you have, where it is or what you are trying to achieve unless you tell us and/or show us. Some further information would help.

 

[AhoyNC]

Are you tgrying to do conditional formatting?

If so this formula should work.
-Highlight the cells A1 to D1
-CONDITION FORMATTING
-New RULE
-Use Formula
-Then enter formula
-Choose Formate

Excel Workbook
	A	B	C	D	E	F
1	6	22	26	77		26
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =IF(ISBLANK($F$1),0,ISNUMBER(MATCH($F$1,$A$1:$D$1,0)))	Abc
B1	1. / Formula is =IF(ISBLANK($F$1),0,ISNUMBER(MATCH($F$1,$A$1:$D$1,0)))	Abc
C1	1. / Formula is =IF(ISBLANK($F$1),0,ISNUMBER(MATCH($F$1,$A$1:$D$1,0)))	Abc
D1	1. / Formula is =IF(ISBLANK($F$1),0,ISNUMBER(MATCH($F$1,$A$1:$D$1,0)))	Abc
F1	1. / Formula is =IF(ISBLANK($F$1),0,ISNUMBER(MATCH($F$1,$A$1:$D$1,0)))	Abc

 

[Jimboexcel]

You're right Peter, I should have been more specific.

Basically, the formula is in one cell as a search box. The purpose is that if I enter the number 77 for example, it highlights the entire row where the number 77 is located. However the problem is when I enter 77, anything with a 7 in it is highlighted.

 

[Jimboexcel]

Actually, the other way around, if I enter 7, anything with 7 is highlighted, 73, 77, etc.

AhoyNC, I tried that, however same problem arises.

 

[Peter_SSs]

Try this. My search box is J1.
I selected A1:E9 and applied the Conditional Formatting formula shown.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	19	15	1	9	8					12
2	17	8	1	16	17
3	9	15	7	2	12
4	13	15	13	1	19
5	20	20	1	11	2
6	17	13	2	7	5
7	3	17	12	3	3
8	18	16	16	7	2
9	12	9	17	5	16
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =COUNTIF($A1:$E1,$J$1)	Abc

 

[Jimboexcel]

Thanks Peter, it worked and is much simpler. I'm from downunder too, enjoy your day.

 

[Peter_SSs]

Thanks Peter, it worked and is much simpler. I'm from downunder too, enjoy your day.

You're welcome.

FWIW, AhoyNC's suggestion also works for me. It's just that if you were applying it to a number of rows in columns A:D, you would need to remove these two $ signs.

=IF(ISBLANK($F$1),0,ISNUMBER(MATCH($F$1,$A$1:$D$1,0)))

.. and it has more checks in it than needed I believe. This should also work for data in columns A:D and search bow in F1

=MATCH($F$1,$A1:$D1,0)