查找第二个vlookup值 [英] Find second vlookup value

问题描述

我在工作表"SKU DATA"的单元格J2中具有以下vlookup公式...

I have the following vlookup formula in Cell J2 of sheet 'SKU DATA' ...

=VLOOKUP(A2,DUPLICATE!C:AJ,34,0)

但是，在"DUPLICATE"工作表范围的第34列中，有2种可能的结果(最多只能有2种结果).

But, in column 34 of the 'DUPLICATE' sheet range there are 2 possible results (there will only ever be a maximum of 2 results).

Vlookup显示了第一个结果...是否可以在单元格J3中使用公式来显示第二个结果?

Vlookup shows the first result ... is there a formula that I can use in cell J3 to show me the second result?

我看过几个match，index和small函数，但是什么都无法工作?

I've looked at several match, index and small functions but, can't get anything to work ?

谢谢

推荐答案

我敢肯定有很多方法可以做到这一点(这可能不是最有效的)，但是首先想到的是找到复制工作表的C列中的第一个匹配项，然后从此处开始VLOOKUP().

I'm sure there are MANY ways to do this (and this may not be the most efficient), but the first that comes to mind would be to find the first match in column C of your Duplicate sheet and then start your VLOOKUP() from there.

如下所示:

=VLOOKUP(A2,OFFSET(DUPLICATE!$C$1,MATCH(A2,DUPLICATE!C:C,0),0,1000,34),34,0)

本质上:

1. MATCH()函数将匹配第一个匹配项并返回该行号
2. OFFSET()然后创建一个范围参考，该范围参考从C列开始，但是在该匹配之后排1行，深度为1000行，宽度为34列
3. 然后您在该范围内进行Vlookup

1. The MATCH() function will match the first occurrence and return that row number
2. The OFFSET() then creates a range reference starting at column C, but 1 row after that match and 1000 rows deep and 34 columns wide
3. You then do your Vlookup in that range

这将返回第二个引用.

希望是有道理的！

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