因此有人被要求使用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(取决于下拉列表)
发布评论