Lookup returns message box when an exact match is not found

I have searched this and other boards for an answer. Hope you can help me
out, other answers do not exactly address my formula.
=LOOKUP(Form!C3,Assignments!A3:A194,Assignments!B3:B194)
Where Form!C3 contains a franchise number the user types in. If the number
does NOT exist, I need to inform (i.e., a message box) the user that an
invalid number has been entered.

The nearest match is not an option for this form.

Thanks

=IF(COUNTIF(Assignments!A3:A194,Form!
C3),your_formula_here,"Invalid Number")

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have searched this and other boards for an answer.
Hope you can help me
>out, other answers do not exactly address my formula.
>=LOOKUP(Form!C3,Assignments!A3:A194,Assignments!B3:B194)
>Where Form!C3 contains a franchise number the user types
in. If the number
>does NOT exist, I need to inform (i.e., a message box)
the user that an
>invalid number has been entered.
>
>The nearest match is not an option for this form.
>
>Thanks
>
>
>.
>

Hi

And when you have many cells with VLOOKUP formulas, and several of them
(p.e. 100) don't find the match? :-))

Really, activating the message box is an action. And VLOOKUP is a function.
By definition, functions can't invoke any actions, like select/activate a
cell, or run a macro, or change the entry in any cell. They only can display
a result.
NB! When you enter into some cell the formula p.e. =A1+B1, and it displays
p.e. 5, then the entry isn't 5, but the formula. And whatever values you
enter into cells A1 or B1 - the entry in cell with formula remains same -
the formula.

The nearest option:
=IF(ISERROR(VLOOKUP(...)),"",VLOOKUP())
or
=IF(ISERROR(VLOOKUP(...)),"No matching value!",VLOOKUP())

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"JFeeman" <[email protected]> wrote in message
news:[email protected]...
> I have searched this and other boards for an answer. Hope you can help me
> out, other answers do not exactly address my formula.
> =LOOKUP(Form!C3,Assignments!A3:A194,Assignments!B3:B194)
> Where Form!C3 contains a franchise number the user types in. If the number
> does NOT exist, I need to inform (i.e., a message box) the user that an
> invalid number has been entered.
>
> The nearest match is not an option for this form.
>
> Thanks
>
>

Thanks for all the responses; I am glad you knew what I meant. Thanks.

Jack

"Arvi Laanemets" wrote:

> Hi
>
> And when you have many cells with VLOOKUP formulas, and several of them
> (p.e. 100) don't find the match? :-))
>
> Really, activating the message box is an action. And VLOOKUP is a function.
> By definition, functions can't invoke any actions, like select/activate a
> cell, or run a macro, or change the entry in any cell. They only can display
> a result.
> NB! When you enter into some cell the formula p.e. =A1+B1, and it displays
> p.e. 5, then the entry isn't 5, but the formula. And whatever values you
> enter into cells A1 or B1 - the entry in cell with formula remains same -
> the formula.
>
> The nearest option:
> =IF(ISERROR(VLOOKUP(...)),"",VLOOKUP())
> or
> =IF(ISERROR(VLOOKUP(...)),"No matching value!",VLOOKUP())
>
> --
> When sending mail, use address arvil<at>tarkon.ee
> Arvi Laanemets
>
>
> "JFeeman" <[email protected]> wrote in message
> news:[email protected]...
> > I have searched this and other boards for an answer. Hope you can help me
> > out, other answers do not exactly address my formula.
> > =LOOKUP(Form!C3,Assignments!A3:A194,Assignments!B3:B194)
> > Where Form!C3 contains a franchise number the user types in. If the number
> > does NOT exist, I need to inform (i.e., a message box) the user that an
> > invalid number has been entered.
> >
> > The nearest match is not an option for this form.
> >
> > Thanks
> >
> >
>
>
>