vba 如果返回 #N/A，则向此 INDEX MATCH 公式添加条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14653784/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add condition to this INDEX MATCH formula if it returns #N/A
提问by Mowgli
I have a formula in Sheet3 to look up a value from sheet1 and return it, but sometimes, if values are not in sheet1, I want it to check in sheet2. sheet1 and sheet2 have same data in column A:A, only columns have different values.
我在 Sheet3 中有一个公式可以从 sheet1 中查找值并返回它，但有时，如果值不在 sheet1 中，我希望它检查 sheet2。sheet1 和 sheet2 在 A:A 列中具有相同的数据，只有列具有不同的值。
=INDEX(Sheet1!D:D,MATCH(Sheet3!A2&"MAN_CHANGE",Sheet1!A:A,0))
How can I modify the formula to check in sheet2 if there's not a match in sheet1?
如果 sheet1 中没有匹配项，如何修改公式以签入 sheet2？
回答by Peter L.
Glad to hear from you again!) Try this please:
很高兴再次收到您的来信！）请试试这个：
=INDEX(Sheet1!D:D,IFERROR(MATCH(Sheet3!A2&"MAN_CHANGE",Sheet1!A:A,0),MATCH(Sheet3!A2&"MAN_CHANGE",Sheet2!A:A,0)))
回答by scott
iferror will check for any error result from the index formula
iferror 将检查索引公式的任何错误结果
=iferror(INDEX(Sheet1!D:D,MATCH(Sheet3!A2&"MAN_CHANGE",Sheet1!A:A,0)),"NOT FOUND")
Alternatively you could use isNA to search just for #N/A
或者，您可以使用 isNA 仅搜索 #N/A
=if(ISNA(INDEX(Sheet1!D:D,MATCH(Sheet3!A2&"MAN_CHANGE",Sheet1!A:A,0)),"NOT FOUND",INDEX(Sheet1!D:D,MATCH(Sheet3!A2&"MAN_CHANGE",Sheet1!A:A,0)))
回答by chrisso
I nested two INDEX,MATCH formulas and it worked.
我嵌套了两个 INDEX,MATCH 公式并且它起作用了。
=IFERROR(INDEX('Activity Report 11-30-17'!G:G,MATCH('Recon Report 11-30-17'!C2,'Activity Report 11-30-17'!D:D,0)),INDEX('Activity Report 11-30-17'!G:G,MATCH('Recon Report 11-30-17'!D2,'Activity Report 11-30-17'!D:D,0)))
=IFERROR(INDEX('活动报告 11-30-17'!G:G,MATCH('侦察报告 11-30-17'!C2,'活动报告 11-30-17'!D:D,0)) ,INDEX('活动报告 11-30-17'!G:G,MATCH('侦察报告 11-30-17'!D2,'活动报告 11-30-17'!D:D,0)))
