=IF(MATCH(J1492,books!$A$1:$Z$300),"",1)

[kylefoley76]

I can't figure out what's wrong with the above formula. It's coming back #N/A. I'm trying to return a blank if the value in J matches any word found on the books! spreadsheet, and 1 if the value in J is never found on the books spreadsheet.

 

[jasonb75]

If match returns n/a then the formula it's in will also return n/a if you don't use an error handling function.

=IF(ISNUMBER(MATCH(J1492,books!$A$1:$Z$300)),"",1)

alternatively try

=if(countif(books!$A$1:$Z$300,J1492),"",1)

 

[kylefoley76]

I can't get it to work. This is very bizarre. It worked for two values, but it did not work for some other values and it clearly should.

I'm using this code

=IF(ISNUMBER(MATCH(J1492,books!$A$1:$AZ$300)),"",1)

and it's showing that none of them match.

When I change it to

=IF(ISNUMBER(MATCH(J1492,books!$h$1:$h$300)),"",1)

It will work for two of 9 values, but one of the values is not even in column h

 

[jasonb75]

Something I didn't notice before, match is no use for what you want, it only works with a single row / column. A1:A300 would work, as would A1:AZ1, but that would mean many formula for each cell.

In addition, MATCH(J1492,books!$h$1:$h$300,0) you didn't have exact match specified so it was accepting the closest.

If you try the alternative I suggested that should work with no problems.

 

[kylefoley76]

Well, How do I get it to check the whole spreadsheet?

 

[jasonb75]

Well, How do I get it to check the whole spreadsheet?

Look at the last line of my previous reply, then the last 2 lines of my first reply.

 

[kylefoley76]

Ok, I got 9 columns, so maybe I can do it in one formula. I tried it for these columns but it didn't work

=IF(ISNUMBER(MATCH(J1499,books!$D$1:$D$300&books!$H$2:$H$300&books!$K$1:$K$300,0)),"",1)

It's returning 1 for everything and it should be returning blanks

 

[jasonb75]

Why are you so determined to use match when i've already said it's the wrong function for the task, and given you an alternative that will work.

=if(countif(books!$A$1:$Z$300,J1492),"",1)