使用INDEX MATCH查找大于/等于值 [英] Use INDEX MATCH to find greater than/equal to value
问题描述
我一直在网上和Excel帮助中搜索，但是找不到是否可以。
给定一个数字，值和搜索字词的列表，我需要以找到值大于或等于搜索项的值。参见示例：
Excel返回红色，但由于15大于10，因此应返回橙色。
其他所需的结果示例是：
- 5 =红色（或不适用）
- 25 =黄色
- 63 =紫罗兰
- 15434 =紫罗兰（或不适用）
我意识到，如果我按降序对列表进行排序，并使用 -1 作为 MATCH [match_type] 参数，那么它的工作正常，但，列表超过1,000行，更容易阅读升序
您想要的结果显示有点不一致。为什么15个橙色需要的结果是63个Indigo？
您是否真的尝试根据绝对的相应值进行匹配最接近搜索值，无论是否低于该值？
如果是这样：
= INDEX（B4 ：B10，MATCH（TRUE，INDEX（ABS（A4：A10-B1）= MIN（INDEX（ABS（A4：A10-B1）,,））,,），0））
虽然仍然不会区分例如橙色和黄色的搜索值为25（尽管我不知道你如何在任何情况下考虑25接近30到20）。
/ p>
I've been searching online and in Excel Help but cannot find whether this is possible.
Given a list of numbers, values and a search term, I need to find the value whereby the value is greater than or equal to the search term. See the example:
Excel returns Red but it should return Orange because 15 is greater than 10.
Other desired result examples would be:
- 5 = Red (or N/A)
- 25 = Yellow
- 63 = Violet
- 15434 = Violet (or N/A)
I realise that if I sort the list in descending order and use -1 for the MATCH [match_type] argument, then it works okay, but, the list is over 1,000 rows and is far easier to read in ascending order.
Your desired results appear a little inconsistent. Why is the desired result for 15 Orange but that for 63 Indigo?
Are you in fact attempting to match based on whichever corresponding value is absolutely closest to the search value, whether below or above that value?
If so:
=INDEX(B4:B10,MATCH(TRUE,INDEX(ABS(A4:A10-B1)=MIN(INDEX(ABS(A4:A10-B1),,)),,),0))
though that still won't differentiate between e.g. Orange and Yellow for a search value of 25 (though I'm not sure how you are considering 25 "closer" to 30 than 20 in any case).
Regards
这篇关于使用INDEX MATCH查找大于/等于值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
