Using ISNA function, but replace the blank cell with text

[Minnie]

I have a project that requires a cell to change with different variables.
The cell can either be blank, use a number from a vlookup that is lower than
listed in the vlookup at which time the text message "customer n. > than...
appears, or the number is in the vlookup range, or the number is greater than
the numbers in the vlookup and enters an "NA" result at which I used the ISNA
function which then in turn leaves the cell blank. The cell needs to have
the text "customer n. invalid" in the cell. The following is the formula I
have so far and the only thing it does not do is enter the text "customer n.
invalid" when the result is false.

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than
"&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1),"",IF(ISNA(VLOOKUP(E8,customers!A3:A11,1,FALSE)),"Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE)))))

I would appreciate any help with this. I have spent many hours trying to
make this work. My whole project is full of these type of excel formulas.

 

[Pete_UK]

Try this:

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater
than
"&customers!$A$3,IF(ISNA(VLOOKUP(E8,cu­stomers!
A3:A11,1,FALSE)),"Customer
number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE))))

Hope this helps.

Pete

 

[Minnie]

That's great Pete! It worked for replacing the "NA" result with "customer n.
invalid" but now when I enter a valid number, the valid number also shows up
in the cell that should be blank.

 

[Pete_UK]

I think you want to do this, then:

=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater
than "&customers!$A$3,IF(ISNA(VLOOKUP(E8,customers!
A3:A11,1,FALSE)),"Customer number is invalid","")))

Hope this helps.

Pete