检查“单元格"值是否存在于“列"中，然后获取“下一个"单元格的值 [英] Check if Cell value exists in Column, and then get the value of the NEXT Cell

问题描述

检查一列中是否存在单元格值后，我需要获取匹配单元格旁边的单元格值.例如，我检查column B中是否存在cell A1中的值，并假设它与B5匹配，那么我想要cell C5中的值.

After checking if a cell value exists in a column, I need to get the value of the cell next to the matching cell. For instance, I check if the value in cell A1 exists in column B, and assuming it matches B5, then I want the value in cell C5.

为解决问题的前半部分，我这样做了...

To solve the first half of the problem, I did this...

=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", "Match")

...而且有效.然后，由于关于SO的较早答案，我还能够获得匹配单元格的行号:

...and it worked. Then, thanks to an earlier answer on SO, I was also able to obtain the row number of the matching cell:

=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", "Match on Row " & MATCH(A1,B:B, 0))

很自然地，为了获得下一个单元格的值，我尝试了...

So naturally, to get the value of the next cell, I tried...

=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", C&MATCH(A1,B:B, 0))

...它不起作用.

我想念什么?如何将列号附加到返回的行号上以获得所需的结果?

What am I missing? How do I append the column number to the row number returned to achieve the desired result?

推荐答案

在 t.thielemans的答案之后，我工作了只是

=VLOOKUP(A1, B:C, 2, FALSE) 

可以很好地工作并满足我的要求，除了它为不匹配项返回#N/A之外；因此适用于已知值肯定存在于查询列中的情况.

works fine and does what I wanted, except that it returns #N/A for non-matches; so it is suitable for the case where it is known that the value definitely exists in the look-up column.

编辑(基于t.thielemans的评论):

为避免#N/A出现不匹配，请执行以下操作:

Edit (based on t.thielemans' comment):

To avoid #N/A for non-matches, do:

=IFERROR(VLOOKUP(A1, B:C, 2, FALSE), "No Match")

这篇关于检查“单元格"值是否存在于“列"中，然后获取“下一个"单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！