组合多个VLOOKUP [英] Combine multiple VLOOKUPs

问题描述

How would I combine these together into one?:

=IFERROR(VLOOKUP(B2:B11,Sheet2!A:B,2,FALSE),"No Match")
=IFERROR(VLOOKUP(B3:B12,Sheet2!D:E,2,FALSE),"No Match")
=IFERROR(VLOOKUP(B2:B11,Sheet2!G:H,2,FALSE),"No Match")

只有三个，但最终我需要12个总共加入，因为这些正在搜索队名，总共有12支球队。

These are just three but eventually I would need 12 in total to be joined as these are searching team names and there are 12 teams in total.

推荐答案

如果您想按顺序处理每一列列，只需三个例子：

If you wish to work through each of the pairs of columns in order, for an example of three pairs, just:

=IFERROR(VLOOKUP(B2:B11,Sheet2!A:B,2,0),IFERROR(VLOOKUP(B2:B11,Sheet2!D:E,2,0),IFERROR(VLOOKUP(B2:B11,Sheet2!G:H,2,0),"No Match")))  

假设它是B2这是您的搜索字词（lookup_value），上面的公式在Row2中。在VLOOKUP中使用范围作为搜索项时，使用的行值是公式所在的行（隐含截距）。

assuming that it is B2 that is to be your search term (lookup_value) and that the formula above is in Row2. Where ranges are used as the search term in VLOOKUP the row value used is the one in which the formula resides ("implied intercept").

通常优选（例如一个更简单的）使用这样的版本：

It is generally preferred (eg a tad shorter) to use a version like this instead:

 =IFERROR(VLOOKUP(B2,Sheet2!A:B,2,0),IFERROR(VLOOKUP(B2,Sheet2!D:E,2,0),IFERROR(VLOOKUP(B2,Sheet2!G:H,2,0),"No Match"))) 

并复制公式，当B2自动调整为B3等（更短也是为什么我倾向于更喜欢 0 到 FALSE 。）

and copy the formula down, when B2 adjusts automatically to B3 etc. (Shorter is also why I tend to prefer 0 to FALSE.)

如@Ditto所述，数据具有不寻常的布局。除非你有十二对专栏的特殊原因，否则似乎只有一对是足够的，因为你似乎没有试图将任何一个条件找到哪个B2值。堆叠在列A：B（通过空格分隔它们和单独的团队的标签），不需要复制一个公式，这应该足以代替所有12：

As mentioned by @Ditto, your data has an unusual layout. Unless you have special reasons for twelve pairs of columns it would appear that just one pair should be sufficient as you seem not to be attempting to place any condition upon which team is searched for which "B2 value". Stacked in ColumnsA:B (by all means with spaces to separate them and labels for the separate teams) there would be no requirement to replicate one of your formulae, this should be sufficient in place of all 12:

 =IFERROR(VLOOKUP(B2,Sheet2!A:B,2,0),"No Match")  

如果可以确保在某处找到一个结果 （或＃N / A 不接受，而不是不匹配），可以进一步简化为：

If it can be guaranteed that a result will be found somewhere (or #N/A accepted if not, instead of "No Match") this can be further simplified to:

  =VLOOKUP(B2,Sheet2!A:B,2,0)

这篇关于组合多个VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！