来自不同表的vlookup(取决于下拉列表) [英] vlookup from different tables dependent on a drop down list

问题描述

因此有人被要求使用excel制作技能矩阵"，似乎在逻辑上有些挣扎.我创建了一个下拉列表(x，y，z)，每个选项都引用一个不同的数据集.例如，如果要从列表中选择"x"，我想使用vlookup(特别是从相关表中)返回数据.目的是创建一个表单，该表单将根据最初的选择返回人员和技能.我目前正在使用 = IF(ISERROR(SEARCH("x"，$ B $ 1))，"，VLOOKUP(P13:$ P $ 16，$ P $ 2:$ S $ 16,1))可以正常工作我选择"x".所以基本上我正在寻找可以像这样的东西:如果x是表x的vlookup，则为elif'y'，然后是y的vlookup，否则为z

如果有人可以帮助我，我将非常有义务.

解决方案

我将执行以下操作，对单个值进行一次搜索，因为我不是100％肯定会得到您想要实现的目标:

• 使用名称管理器为您要搜索的每个数据范围建立一个名称(X Y和Z)，并给它们一个方便的名称，例如X_Range，Y_Range，Z_Range
• 在单元格B1中输入要搜索的范围(X，Y或Z)
• 在B2单元格中输入要查找的值

• 使用以下公式在B1中指示的适当范围内搜索单元格B2

  = VLOOKUP(B2; INDIRECT(B1&"_Range"); 2; FALSE)

INDIRECT是关键.它将允许您解析使用范围名称和文字"_Range"作为范围名称构建的字符串.

希望有帮助

So I've been asked to make a 'skills matrix' using excel and seem to be struggling with the logic somewhat. I have created a dropdown list (x,y,z), each of the options refers to a different dataset. For example, if I were to select 'x' from the list I would like to return data, using vlookup, specifically from the relevant table. The intention is to create a form which will return people and skills based on the initial selection. I'm currently using =IF(ISERROR(SEARCH("x",$B$1)),"",VLOOKUP(P13:$P$16,$P$2:$S$16,1)) which works ok if I select 'x'. So basically I'm looking for something which will work like: If x then vlookup from table x, elif 'y' then vlookup from y, else z

If anyone can help me I'd be much obliged.

解决方案

What I would do is the following, with a single search of a single value as I am not 100% sure I got what you want to achieve :

• Use the name manager to build a name for each of your data range to search through (X Y and Z) and give them a convenient name, for example X_Range, Y_Range, Z_Range
• Type in cell B1 the range to search (X, Y or Z)
• Type in cell B2 the value to look for

• Use the following formula to search cell B2 in the proper range you indicate in B1

  =VLOOKUP(B2;INDIRECT(B1 & "_Range");2;FALSE)

INDIRECT is the key. It will allow you to resolve the string that is build with the range name and the literal "_Range" as a range name...

Hope that helps

这篇关于来自不同表的vlookup(取决于下拉列表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！