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

lmoseley7

Board Regular
Joined
Jul 9, 2013
Messages
151
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:
Code:
=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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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))
 
Last edited:
Upvote 0
Hey try this:

=IF(ISNUMBER(L2),1000,ERROR.TYPE(7))
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top