结合了MATCH，INDEX和OFFSET的Excel公式 [英] Excel formula that combines MATCH, INDEX and OFFSET

问题描述

我在使用Excel函数时遇到麻烦.

I am having trouble with an Excel-function.

在工作表A上，我想获取位于单元格F2右侧x列的单元格的值. X是一个可变数字，由单元格A1的值确定.当前值为5.

On sheet A I want to get the value of a cell that is located x-columns to the right of cell F2. X is a variable number and is determined by the value of cell A1. Currently, the value is 5.

=(OFFSET(sheetA!F2,0,sheetA!A1))

此公式有效.但是，我想将此功能包括在另一个工作表(B)上的MATCH和INDEX函数中.

This formula works. However, I want to include this function into a MATCH and INDEX function that is located on another sheet (B).

我知道我可以使用以下公式来获取$ F $ 2的值

I know that I can use the following formula to get value of $F$2

INDEX(sheetA!F:F,MATCH(sheetB!C4,sheetA!A:A,0))

将它们组合起来，得到以下公式:

Combining them, results in the following formula:

=INDEX((OFFSET(sheetA!F2,0,sheetA!A1)),MATCH(sheetB!C4,sheetA!A:A,0))

此公式生成一个#REF！值.

This formula generates a #REF!-value.

如果我计算公式，我会看到以下步骤:

If I evaluate the formula, I see the following steps:

=INDEX((OFFSET(sheetA!$F$2,0,5)),MATCH(sheetB!C4,sheetA!A:A,0))
=INDEX((sheetA!$K$2),MATCH(sheetB!C4,sheetA!A:A,0))
=INDEX((sheetA!$K$2),MATCH("BTC",sheetA!A:A,0))
=#REF!

为什么要使用MATCH和INDEX? 因为虽然工作表A上的值是固定的"，但工作表B！C4的值是浮动/可变的.因此，我需要先找到正确的行.正确的列可以使用offset-part来完成.

Why do I want to use MATCH and INDEX? Because while the values on sheet A are "fixed", the values of sheetB!C4 are floating/variable. Therefore, I need to locate the correct row first. The correct column can be done with the offset-part.

谢谢您的帮助.

推荐答案

尝试一下

=INDEX((OFFSET(SheetA!F:F,0,SheetA!A1)),MATCH(SheetB!C4,SheetA!A:A,0))

INDEX的语法是

INDEX(array, row_num, [column_num])

其中，数组是单元格的范围.当您使用=INDEX((OFFSET(sheetA!F2,0,sheetA!A1)),MATCH(sheetB!C4,sheetA!A:A,0))时，(OFFSET(sheetA!F2,0,sheetA!A1))返回sheetA!$K$2，这是一个单元格而不是范围.

where, array is range of cells. When you use =INDEX((OFFSET(sheetA!F2,0,sheetA!A1)),MATCH(sheetB!C4,sheetA!A:A,0)), (OFFSET(sheetA!F2,0,sheetA!A1)) returns sheetA!$K$2 which is a cell not a range.

这篇关于结合了MATCH，INDEX和OFFSET的Excel公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！