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!B 3: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<attarkon.ee
Arvi Laanemets


"JFeeman" wrote in 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

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<attarkon.ee
Arvi Laanemets


"JFeeman" wrote in 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