index indirect.ext匹配公式 [英] index indirect.ext match formula
问题描述
我在下面使用这个公式。它确实有效，但不幸的是，一旦匹配发现我没有得到正确的列结果。我希望E列中的信息，但我得到另一列的单元格信息。那么我需要做什么才能始终获得E列的结果呢？
I am using this formula below. It does work, but unfortunately once the match has found I do not get the proper column result back. I would expect the information out of column E, but I get the cell info back of another column. So what do I need to do in order to get always the results back of column E?
=INDEX(INDIRECT.EXT("'"&BO13&"
["&BO17&"]"&BO18&"'!"&BU5),MATCH(A75,INDIRECT.EXT("'"&BO13&"
["&BO17&"]"&BO18&"'!"&BU7),0),MATCH(AA75,INDIRECT.EXT("'"&BO13&"
["&BO17&"]"&BO18&"'!"&BU8),0))
- BO13 =路径
- BO17 = Filename.xlsx
- BO18 = SheetName
- BU5 = $ E $ 3：$ E $ 5 - >查找范围
- BU7 = $ B $ 2：$ B $ 5 - >包括某个名字
- BU8 = $ C $ 2：$ C $ 5 - >包括一定数量
- A75 =查找值，这是一个名称
- AA75 =查找值是一个数字
- BO13 = Path
- BO17 = Filename.xlsx
- BO18 = SheetName
- BU5 = $E$3:$E$5 --> lookup range
- BU7 = $B$2:$B$5 --> including a certain name
- BU8 = $C$2:$C$5 --> including a certain number
- A75 = lookup value which is a name
- AA75 = lookup value which is a number
推荐答案
如果出现类似问题，请使用此公式。
INDEX（INDIRECT.EXT（'& BO13&[& BO17&]& BO18&'！& BU5），MATCH（1，（INDIRECT.EXT（ ' &安培; BO13&安培;[ &安培; BO17&安培;] &安培; BO18&安培;'！ &安培; BU7）= A75）*（INDIRECT.EXT（' &安培; BO13&安培;[&安培; BO17&安培;]& BO18&'！& BU8）= AA75），0））
Use this formula in case of similar issues. INDEX(INDIRECT.EXT("'"&BO13&"["&BO17&"]"&BO18&"'!"&BU5),MATCH(1,(INDIRECT.EXT("'"&BO13&"["&BO17&"]"&BO18&"'!"&BU7)=A75)*(INDIRECT.EXT("'"&BO13&"["&BO17&"]"&BO18&"'!"&BU8)=AA75),0))
这篇关于index indirect.ext匹配公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
