带有单元格引用的 Excel Vlookup [英] Excel Vlookup with cell reference

问题描述

我有一个在 Excel 中命名为cell_range"的单元格区域.我想从这个表中提取第四行和第五列.公式，

=vlookup(4,cell_range,5)

给了我我正在寻找的价值.

但是，我在单元格 A1 中也有文本cell_range".我不想在公式中输入cell_range"，而是想通过引用单元格 A1 来间接引用cell_range".公式

vlookup(4,A1,5)

给我一个#N/A"错误.

如何在我的公式中间接引用表格cell_range"?

解决方案

虽然您可以使用 INDIRECT 来执行此类操作，但出于我在

请注意，您需要使用我的方法预先指定您的区域.

这是 CHOOSE 的语法

=CHOOSE(index_num,value1,value2,...)

这是从 Microsoft-speak 到 Jeff-speak 的翻译:

=CHOOSE(你想要哪个区域?，第一个区域，第二个区域，...)

如果使用下拉列表或单元格引用来提供 index_num 参数，您只需使用一个查找表，将下拉列表或单元格输入的输出转换为一个索引号，该索引号告诉 CHOOSE 应使用列表中的哪个范围.

在本例中，您使用这种方法来选择要对电子表格上的哪个区域进行求和.但是 CHOOSE 和 INDEX 可以用来做更多的事情.例如，您可以使用它们来允许用户在执行 VLOOKUP 时动态选择要使用的查找表.或者您可以让用户动态选择用户在哪些工作表中进行某些计算的范围.确实很强大的东西！

I have a cell range that I named "cell_range" in Excel. I want to extract the fourth row and fifth column from this table. The formula,

=vlookup(4,cell_range,5)

gives me the value I am looking for.

However, I also have the text "cell_range" in cell A1. Instead of typing out "cell_range" in my formula, I want to reference "cell_range" indirectly by referencing cell A1. The formula

vlookup(4,A1,5)

is giving me a "#N/A" error.

How can I indirectly reference the table "cell_range" in my formula?

解决方案

While you can use INDIRECT to perform this kind of stuff, I steer clear of it for reasons I've outlined at https://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

In this particular case I'd use a lookup table and either CHOOSE or INDEX, as demonstrated in the screenshot below:

Note that you need to pre-specify your areas with my approach.

Here's the syntax for CHOOSE

=CHOOSE(index_num,value1,value2,...)

Here’s the translation from Microsoft-speak into Jeff-speak:

=CHOOSE(Which area do you want?, First area, Second area, ...)

If using a dropdown or a cell reference to provide the index_num argument, you simply use a lookup table that converts the output of the dropdown or cell input into an index number that tells CHOOSE which range from the list should be used.

In this example, you’re using this approach to choose which area on the spreadsheet to sum. But CHOOSE and INDEX can be used to do a lot more than that. For instance, you could use them to allow a user to dynamically pick which lookup table to use when doing a VLOOKUP. Or you could let the user dynamically pick which range in which sheet to user for some calculations do. Very powerful stuff indeed!

这篇关于带有单元格引用的 Excel Vlookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！