Need help with '=MATCH(TRUE,ISNUMBER(' formula.

[lmoseley7]

I'm trying to write a formula that would check a single cell to see if it contains a number. If it does, it will return 1000, if not it returns an #N/A result. To start, I've written the following simple formula just to let me know if the cell contains a number, but for some reason it doesn't work when a number is present:

=MATCH(TRUE,ISNUMBER(L2),0)

I've tested this formula with a number keyed into L2 and a formula that returns a number and both return a #N/A error. By evaluating the formula, I can see that the 'ISNUMBER(L2)' evaluates to True, but MATCH must not like one True value as a lookup_array. Through my searching, I see normally 'MATCH(TRUE(ISNUMBER(' is often followed by the SEARCH function, but I really just want to confirm that the cell contains a number. I've also tried using 'MATCH(TRUE,L2>0', but that didn't work.

This seems like a simple task, but for some reason I just can't get it to work.

 

[Aryatect]

Hi Try:

=IF(ISNUMBER(L2),1000,"#N/A")


If you truly need #N/A error of excel then use below:

=IF(ISNUMBER(L2),1000,ERROR.TYPE(7))

 

[tyija1995]

Hey try this:

=IF(ISNUMBER(L2),1000,ERROR.TYPE(7))

 

[tyija1995]

Hi Try:

=IF(ISNUMBER(L2),1000,"#N/A")

Hey, just a heads up that will produce a string value "#N/A" and not be flagged as an error, so if you try IFERROR with that value it will accept it instead of flag the latter parameter (value_if_error)

 

[Roger Govier]

Why not simply use
=IF(ISNUMBER(L2),1000,"N/A")

Substitute whatever you wish in place of N/A for what you want if the cell does not containa anumber.

 

[lmoseley7]

Great responses, thanks. I wasn't familiar with how to incorporate the error types in the formula, so thanks for that information.

I'm trying to recreate a formula, the result of which I saw in a YouTube video, and all I saw was #N/A errors where no values were in the related cells and 1000 where the related cells contained numbers. I assumed they were using the MATCH function to return that error and I thought I could combine that with the ISNUMBER function to produce these results. I assumed if the original formula was an 'IF' statement, they wouldn't have forced the error to be visible, but I may be wrong about that. I'd love to understand why the formula I wrote isn't working as I hoped, but these suggestions would work if I need to go in that direction.

Maybe there is another formula that would do the same and it's not an 'IF' statement or 'MATCH(TRUE,ISNUMBER' statement.

 

[tyija1995]

Hey,

You could try the following approach if you don't want an IF or MATCH/ISNUMBER formula:

=CHOOSE(--(N(L2)<>0)+1,ERROR.TYPE(7),1000)

The only downside is it will flag 0 as #N/A - it should work other than that issue though.

 

[lmoseley7]

Thanks for another option.