Excel:检查列中是否存在单元格值，并返回同一行不同列的值 [英] Excel: Check if Cell value exists in Column, and return a value in the same row but different column

问题描述

检查列中是否存在值后，我想将同一行中单元格的值返回到不同的列.

After checking if a value exists in a column, I want to return the value of the cell in the same row to a different column.

具体来说，我正在检查 O 列中的任何值是否与 A 列中的值匹配.如果匹配，我想从与 A 列中匹配项相同行的单元格中获取相应的值，但这些值在 f 列中.然后我想从 f 列中取出这些值，并将它们放在与 O 列中的值相同的行中.

Specifically, I am checking to see if any values in column O match with values from column A. If they do I want to take the corresponding value from cells in the same row as the matched items in column A, but these values are in column f. I then want to take those values from column f and put them in the same rows as the values from column O.

这是我试过的公式:

=IF(ISNA(MATCH(O2,$A$2:$A$1589,0)),"no match", VLOOKUP(O2,$A$1:$z$14000,16,FALSE))

这会为 P 列中的所有单元格返回不匹配".我尝试在 VLOOKUP 函数中处理 col_index_num，但我能做的最好的事情是让它返回零，这不正确无论如何，价值观.

This returns a "no match" for all the cells in the column P. I tried messing around with the col_index_num in the VLOOKUP function, but the best I can do is get it to return zeros, which aren't the right values anyway.

推荐答案

我认为以下公式应该可以提供您想要的结果.如果我正确理解您的问题，您希望返回与匹配项位于同一行的 F 列中的值:因此，我用于 F 列的范围与 A 列的范围长度相同.

I think the following formula should give you what you are trying to get. If I understand your question correctly, you want to return the value in column F that is in the same row as the match: hence, the range I use for column F is the same length as the range for column A.

  =IFERROR(INDEX($F$2:$F$1589,MATCH(O2,$A$2:$A$1589,0),1),"no match")

向外工作，这是正在发生的事情.

Working outward, here is what is going on.

• 匹配函数正在 A 列中查找与 O2 中的值完全匹配的值.

• The match function is looking in column A for an exact match of the value in O2.

如果找到匹配项，则返回匹配值在列 A 查找范围中的相对位置.

If it finds a match, it returns the relative position of the matching value in the column A lookup range.

如果没有找到匹配项，则返回一个错误值.

If it finds no match, it returns an error value.

索引函数返回索引范围的第 i 行和第 j 列中的值，在本例中是在索引范围内找到的行匹配，以及索引范围内的第一列(也是唯一一列)，列 F.

The index function returns the value in the i th row and j th column of the index range, in this case the row that was found in the match, and the first (and only) column in the index range, column F.

最后，这两个函数被封装在一个 IFERROR 函数中，以捕获不匹配时将生成的错误值，并返回字符串不匹配".

Finally, those two functions are wrapped in an IFERROR function, to catch the error value that will be generated if there is no match, and return instead the string "no match".

此公式将输入到单元格 P2 中并向下复制到 O 列中的最后一行数据.

This formula would be entered in cell P2 and copied down through the last row of data in column O.

这篇关于Excel:检查列中是否存在单元格值，并返回同一行不同列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！