Using IF with SEARCH

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am trying to get a couple functions to work together. I have a sheet with
unique numbers in D17:D2021. 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
D17:D2021 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=D17:D2021,SEARCH(E9,D17:D2021),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.
 
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
D17:D2021 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 D17:D2021 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.
 
=IF(ISNA(MATCH(E9,D17:D30,0)),"nv","found at D "&MATCH(E9,D17:D30,0)+17)
or Bob's
=IF(NOT(ISNUMBER(MATCH(E9,D17:D2021,0))),"Not Valid","found at D" &
MATCH(E9,D17:D2021,0)+17)
 
Back
Top