INDEX/MATCH返回错误的值.我做错了什么? [英] INDEX/MATCH Returning the wrong value. What did I do wrong?

问题描述

我正在尝试创建一个电子表格，其中INDEX/MATCH会自动填充每月的销售目标.但是，它返回了错误的数字.

I'm trying to create an spreadsheet where INDEX/MATCH automatically populates monthly sales goals. However, it is returning the wrong number.

在B6单元格中，我希望该值为10000.在G和H列中，我已经设定了目标和相应的日期，希望将目标与第2行中的日期匹配.

In cell B6 I want the value to be 10000. In columns G and H, I've set the goals and the corresponding date in the hopes of matching the goal to the date in row 2.

B6返回"6000".AND"7/19/2015"；当它应该只是返回"10000"时，在相邻单元格中...而且我不知道为什么.

B6 is returning "6000" AND "7/19/2015" in an adjacent cell when it should just be returning "10000" ... and I don't know why.

在H6中，将G6单元格中2015年7月12日的目标设置为10,000，这对应于B2中的日期，因此我不知道为什么它在我匹配的日期之后返回日期.

In H6 the goal for the 7/12/2015 is set at 10,000 from cell G6, which corresponds to the date in B2, so I don't know why it is returning the date AFTER the one I'm matching.

谢谢

推荐答案

公式的第二部分，即匹配部分，必须具有"H3:H9"，而不是G.匹配项正在寻找您的B2(日期)在$ G $ 3:$ G $ 9范围内，该数字是数字，因此不会返回日期.

The second part of the formula, the match part, needs to have "H3:H9", not G. The match is looking for your B2 (a date) in the range $G$3:$G$9, which is numbers so there won't be a return of the date.

@Jeeped很好，因为将在表中找到日期，所以最好在结尾处使用"0"而不是"1"-"0"将寻找一个完全符合.如果您保留1，则它可能会返回假阳性(返回的日期与您的查找日期相近，而不是确切的日期).

And @Jeeped makes a good point, since the dates are going to be found in your table, you would probably be better using "0" instead of "1" at the end - "0" will look for an exact match. If you keep 1, it might return a false positive (return a date that is NEAR your lookup date, not the exact one).

这篇关于INDEX/MATCH返回错误的值.我做错了什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！