Using IF with SEARCH

[Mike]

I am trying to get a couple functions to work together. I have a sheet with
unique numbers in D172021. I have data in columns C, E, F, G, H, I, and J
related to the cell under the D column. I want to enter any of the numbers
in column D (example - 1061) into cell E9 and I need Excel to find that
number and display it. If the number entered into E9 is not found in
D172021 I want E9 to display "Not Valid". I have frozen the pane below the
column titles. My thought was to enter the following formula into an unused
column and hide it
=IF(E9=D172021,SEARCH(E9,D172021),E9="Not Valid")
I was not surprised when I received a #VALUE! into the cell the formula is
in. I could be close, or I could be so far off with this. Any assistance
would be appreciated.

 

[Bob Phillips]

=IF(NOT(ISNUMBER(MATCH(E9,D172021,0))),"Not Valid","")

 

[Mike]

Thanks for the reply. This does not quite do what I am looking for. When I
pasted the formula, the cell the formula appeared blank but the formula bar
shows the formula. When I enter the number I am searching for in range
D172021 into cell E9 nothing happens. When I enter a number that is not
found in that range, the cell containing your formula says Not Valid.

What I need if for Excel to find the cell in the D172021 range containing
the number I enter in E9. Basically this will act as a search box. If the
number I key into E9 is not found, I need E9 to display Not Valid.

 

[Don Guillett]

=IF(ISNA(MATCH(E9,D1730,0)),"nv","found at D "&MATCH(E9,D1730,0)+17)
or Bob's
=IF(NOT(ISNUMBER(MATCH(E9,D172021,0))),"Not Valid","found at D" &
MATCH(E9,D172021,0)+17)