If(isnumber(match error not returning correct values for all cells.

[vnnn0203]

Hello,

I am currently using the (If(Isnumber(match

function. I have two sheets. I want the formula to return true if a number on one sheet exists within the dataset in another sheet, and no if it doesn’t. Currently my formula works for some cells, it shows true for cells that do have existing numbers and no for some that don’t.

The issue is there are some numbers that do not exist in the dataset, but are still populating as Yes, even though the formula works on majority of cells.

[#2]

Also, please note that if I paste the entire dataset into the main sheet and reference the search range from there, it returns the correct value.

[#3]

@vnnn0203 It's difficult to troubleshoot without seeing the entire formula you've attempted or a sample of the data set, so I'm just guessing here...

It sounds like the ISNUMBER/MATCH combo is incorrectly retuning TRUE for some values when they no longer exist in the lookup_array, but also correctly returning FALSE for other values. There may be a combination of errors causing this behavior, but the first thing to check is that you've set the optional [match_type] argument of the MATCH function to 0 (exact match). For example:

=IF(ISNUMBER(MATCH(A2, Sheet2!$A$2:$A$11, 0)), "Yes", "No")

If you omit the optional argument, by default it will find the largest value that is less than or equal to the lookup_value (if the lookup_array is sorted in ascending order), which can return unexpected results.

A less likely cause for this particular scenario might be inconsistent data types, i.e. if some of your lookup_values are not actually numbers, but rather are numbers formatted as text. To help diagnose if this a factor, try using the COUNTIF function instead:

=IF(COUNTIF(Sheet2!$A$2:$A$11, A2), "Yes", "No")

When dealing with numbers, the MATCH function is "type" specific, whereas COUNTIF is not. IF/COUNTIF will return the correct results even if the lookup value is a number formatted as text and the lookup range consists of actual numbers (or vice versa).

If neither of the above suggestions solves the problem, please share more details so that someone can better assist you. Kind regards.