Search code examples
if-statementgoogle-sheetsgoogle-sheets-formula

How can I fix If NO MATCH #N/A?

I'm working on a schedule table to pull the corresponding working hours for each shift that match.

I have the following data:

CELL 1: E694

CELL 2: E356

TABLE 1: $AL$10:$AL$16

TABLE 2: $AL$1031:$AL$1037

TABLE 3: $AM$1031:$AM$1037

Here's the intended scenario:

If CELL 1 MATCH TABLE 1, then MATCH CELL 2 with TABLE 2 and pull data from TABLE 3. If CELL 1 DON'T MATCH TABLE 1, MATCH CELL 1 with TABLE 2 and pull data from TABLE 3.

I tried the following formula:

=IF(MATCH(E694,$AL$10:$AL$16,0), timevalue(INDEX($AM$1031:$AM$1037,MATCH(E356,$AL$1031:$AL$1037,0),0))*24, timevalue(INDEX($AM$1031:$AM$1037,MATCH(E694,$AL$1031:$AL$1037,0),0))*24)

The formula only works when there's a MATCH, but If there's no MATCH it doesn't execute the false condition and returns #N/A error.

I know I'm missing something here, but I just can't figure it out.

Would someone be able to give me a hand here?

P.S: here's a simple test sheet (not with the exact same values, but the same formula and logic)

https://docs.google.com/spreadsheets/d/1Wvw-K7iZ6TnID-SzLt2CtOxu4Xx1BuhAjexU_onRASg/edit?usp=sharing

Solution

  • ISNA is most likely what you are wanting, though ISERR or ISERROR could also be applicable depending on the situation.

    A couple notes:

    • I duplicated your original sheet (see the "Fixes" tab on your Google Spreadsheet), and tightened up the layout slightly so I could better understand what you're trying to accomplish.
    • I'm using named ranges in your formula for the tables now, instead of absolute A1 notation ranges. They are easier to maintain, less error prone, and less confusion when writing formulas.
    • I removed the timevalue(...) * 24 you were using to convert the formatted DateTime strings to TimeSpans. Instead, I set that column format to TimeSpan (h:mm), which does exactly the same thing without cluttering our formulas.
    • "Formula", "Cell 1", and "Cell 2" I separated into columns, so that you and I could test and verify the formula on multiple variations of the cell values at once. The 8 rows you see now are 8 variations of cell 1 and cell 2 ("Formula" column is the same same for all test rows).

    The updated working formula is below, based on my best interpretation of your description.

    =IF(
COUNTIF(table1, B1), 
INDEX(table3, MATCH(C1, table2, 0)), 
INDEX(table3, MATCH(B1, table2, 0)))

    As seen in the help popup in the image below, an IF statement evaluates the first argument (logical_expresion), which must be a value that can be converted into a boolean value (true/false).

    Since numbers can represent a bool value (0 = false, anything else = true), and all we need for the first argument is a yes/no, I replaced the first MATCH function with a simple COUNTIF, which simply returns the number of cells in table1 that match B1. Unlike the MATCH function, which returns N/A if no matches are found, COUNTIF simply returns a 0. Since 0 is the same as false, our IF statement knows to use our second MATCH instead of the first.

    enter image description here

    Note regarding my original answer: The first solution I provided used ISNA(MATCH(...)) = false instead of COUNTIF(...). This works also, but is unnecessarily complicated: ISNA returns true if the value of it's argument is "N/A", true otherwise, effectively converting our MATCH into a bool value. That bool value had to be inverted, however, so that "N/A" value would be false, and a successful match would be true. =false was just used to return the opposite value that ISNA gave us. Does false=false? yes, so we return true. Does true=false? no, so we return...false.