excel之间的值或在范围内搜索并返回值 [英] Value between or search in range and return value in excel

问题描述

大家好，

我有一个员工得分级别的表(表D1至F6)，每个级别都有得分，例如，得分(4.60)将在第3个级别中得分(3).

I have a table for employee scores levels (Table D1 to F6), there is points for each level, for example score (4.60) will be in the 3rd level which has (3) points.

我想在(B)列中编写一个公式以检查表中是否从F列返回了点值.您可以在D2& A中看到示例. D3.

I want to write a formula in the column (B) to check in the table an return the point value from the column F, You can see the examples in D2 & D3.

问候 阿德尔

推荐答案

您需要的是有史以来最强大的功能之一... VLOOKUP

What you need is one of the most awesome functions ever invented... VLOOKUP

=VLOOKUP(A2,$D$2:$F$6,3, TRUE)

(粘贴B2并向下拖动)

参数的快速解释.

1. A2是我们要在表中查找的值
2. $D$2:$F$6是我们要查找的表".它将搜索它的第一列(您可以在列D中有所需的任何内容，VLOOKUP只看第一列).
3. 3是表的列号，其中包含我们要查找并返回的答案(在本例中为F).
4. TRUE意味着我们要搜索范围，而不仅仅是精确值.如果是FALSE，则只有输入精确的分数，我们才会得到想要的数字.

1. A2 is the value we're going to be looking up in the table
2. $D$2:$F$6 is the "table" we're going to be looking up. It's going to search the first column of it (You could have anything you wanted in Column D, VLOOKUP Only looks at the first column).
3. 3 is the column number of our table that contains the answers we want to be looking up, and returning (in this case, F).
4. TRUE means we want to search ranges, not just exact values. If it was FALSE then we'd only get the numbers we wanted if we entered the precise scores.

当然，使用此方法没有任何上限，值为20表示最后一行(即5分)，但是，您可以使用IF语句轻松解决此问题

Of course, using this method, there isn't any upper bounds to it, a value of say 20 would give us the last row (i.e. 5 points), however, you could fix this easily using an IF statement.

=IF(A2 > $E$6, "N/A", VLOOKUP(A2,$D$2:$F$6,3, TRUE))

如果您认为稍后要向表中添加更多行，那么当然可以将其整理得更整洁，但是现在可以使用.

This could of course be done a bit neater if you believed you were going to be adding more rows to the table later, but it works for now.

这篇关于excel之间的值或在范围内搜索并返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！