Excel Vlookup返回带有数字的NA [英] Excel Vlookup returns NA with numbers

问题描述

我在Excel中有一个表，其中包含2列文本和2列数字.这些数字是指在文本正文中找到搜索字符串的位置-如果找不到它们，则该单元格将保留为空(例如ISERROR(myfunctions()，"). (我没有足够的代表来嵌入屏幕截图...)

I have a table in Excel which contains 2 columns of text, and two columns of numbers. The numbers refer to the position in which a search string is found within a body of text - and if they aren't found then the cell is left empty (eg ISERROR(myfunctions(),""). A screenshot is here (I dont have enough rep to embed a screenshot...)

我希望在第一列中返回值，其中最小值和最大值来自两个数字列.这对我来说很简单:

I wish to return the value in the first column where the minimum and maximum values are from both of the number columns. This to me is simple:

= VLOOKUP(MIN(E3:E7)，C3:E7,1)

=VLOOKUP(MIN(E3:E7),C3:E7,1)

= VLOOKUP(MAX(E3:E7)，C3:E7,1)

=VLOOKUP(MAX(E3:E7),C3:E7,1)

无论我尝试什么，此结果都为#N/A.是什么原因呢?

This evaluates to #N/A no matter what I try. What is the reason for this?

推荐答案

VLOOKUP始终在查找表的第一列中进行查找，因此您在weather列中查找Min值，但它不匹配.

VLOOKUP always does the lookup on the first column in the lookup table so you are looking up the Min value in the weather column and it does not match.

您可以重组数据以使位置列位于第一位，也可以使用INDEX MATCH

You can either restructure your data so that the location column is first or use INDEX MATCH

=INDEX(C3:E7,MATCH(MIN(E3:E7),E3:E7,0),1)

这篇关于Excel Vlookup返回带有数字的NA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！