努力从VLOOKUP创建函数并将MIN烘焙到其中 [英] Struggling to create a function from VLOOKUP with MIN baked into it

问题描述

我尝试使用VLOOKUP解决有关Excel/VBA的问题，但是，在某些情况下，它可能无法返回正确的值.假设我在Excel中有以下列表

I've got a problem regarding Excel/VBA that I've tried to solve using VLOOKUP, however, it may in some cases not return the correct value. Let's say I have the following list in Excel

Country     Years
Germany      63
Germany      27
Germany      29
France       45

和在另一个工作表中，我有一个包含法国和德国的下拉列表.我想找到一个可以找到我所选择的国家/地区的最小年份的函数.在标准函数VLOOKUP中，对于德国，我只会得到63，并且尝试烘烤MIN也不起作用.有什么办法可以创建自己的功能(在VBA中)，该功能可以完成我描述的操作?

and in another worksheet I have a drop-down list containing France and Germany. I would like to find a function that can find the minimum years of the country I have selected. In the standard function VLOOKUP I would only get 63 for Germany, and attempting to bake in MIN also does not work. Is there any way I could create my own function (in VBA) that could do what I described?

推荐答案

如果您有Office 365，请使用:

If you have Office 365 use:

=MINIFS(B:B,A:A,E2)

如果您有2010年或更高版本，则:

If you have 2010 or later then:

=AGGREGATE(15,6,B2:B5/(A2:A5=E2),1)

如果在2010年之前，请使用以下数组公式:

If it is prior to 2010 then use this array formula:

=MIN(IF(A2:A5=E2,B2:B5))

作为数组公式，在进入编辑模式时必须使用Ctrl-Shift-Enter而不是Enter进行确认.

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when esiting edit mode.

这篇关于努力从VLOOKUP创建函数并将MIN烘焙到其中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！