CODEWITHSUNDEEP
excelvlookup
Chef1075
Chef1075

Reputation: 2724

INDEX/MATCH Returning the wrong value. What did I do wrong?

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

enter image description here

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 is returning "6000" AND "7/19/2015" in an adjacent cell when it should just be returning "10000" ... and I don't know why.

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.

Thanks

Upvotes: 2

Views: 32363

Answers (2)

BruceWayne
BruceWayne

Reputation: 23285

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.

Edit: 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).

Upvotes: 5

rwilson
rwilson

Reputation: 2145

Place in B6 and copy across:

=INDEX($G3:$G9,MATCH(B2,$H3:$H9,1))

Upvotes: 2

Related Questions