英文:
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
英文:
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
答案1
得分: 2
ISNA
最有可能是您想要的,尽管根据情况,ISERR
或 ISERROR
也可能适用。
一些注意事项:
- 我复制了您的原始工作表(请参阅您的 Google 电子表格上的“修复”选项卡),并稍微调整了布局,以便更好地理解您想要实现的目标。
- 我现在在您的公式中使用了命名范围,而不是绝对的 A1 符号范围。这样做更容易维护,更不容易出错,并且在编写公式时更不容易混淆。
- 我删除了您用于将格式化的日期时间字符串转换为时间跨度的
timevalue(...)*24
部分。相反,我将该列的格式设置为时间跨度(h:mm
),这样可以在不使公式混乱的情况下执行相同的操作。 - “公式”、“单元格1”和“单元格2”我分成了不同的列,以便您和我可以同时测试和验证多个单元格值的不同变化。您现在看到的8行是单元格1和单元格2的8种变化(“公式”列对所有测试行都是相同的)。
基于我对您描述的最佳理解,下面是更新后的工作公式:
=IF(
COUNTIF(table1, B1),
INDEX(table3, MATCH(C1, table2, 0)),
INDEX(table3, MATCH(B1, table2, 0)))
如下面的图像中弹出的帮助中所示,IF
语句评估第一个参数(logical_expression
),该参数必须是可以转换为布尔值(true/false)的值。
由于数字可以表示布尔值(0 = false,其他任何值 = true),并且我们只需要第一个参数的是/否,因此我用一个简单的 COUNTIF
替换了第一个 MATCH
函数,它只返回与 B1
匹配的 table1
中的单元格数量。与 MATCH
函数不同,如果找不到匹配项,COUNTIF
简单地返回0。由于0与false相同,我们的 IF
语句知道使用第二个 MATCH
而不是第一个。
*关于我原始答案的说明:*我最初提供的第一个解决方案使用了 ISNA(MATCH(...)) = false
而不是 COUNTIF(...)
。这也可以工作,但是过于复杂:ISNA
如果其参数的值为“N/A”,则返回true,否则返回true,实际上将我们的 MATCH
转换为布尔值。然而,必须反转该布尔值,以便“N/A”值为false,而成功的匹配为true。=false
仅用于返回与 ISNA
给我们的相反值。false=false吗?是的,所以我们返回true。true=false吗?不是,所以我们返回...false。
英文:
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.
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论