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

问题描述

我有一个 Excel 表格，其中包含两列文本和两列数字.数字指的是在文本正文中找到搜索字符串的位置 - 如果找不到，则该单元格留空(例如 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 总是在查找表的第一列上进行查找，因此您正在查找天气列中的最小值并且它不匹配.

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屋！