CODEWITHSUNDEEP
google-sheetsmatchgoogle-sheets-formulaarray-formulasgs-vlookup
HM Production
HM Production

Reputation: 37

Why is my INDEX/MATCH function returning a wrong value?

I am currently trying to make a sequence number generator in Google Sheets, and everything was going smoothly.

However, in the final steps, I noticed that one of my functions was returning the wrong value. Image 1

As you can see in C9, it returns the AMS20-00001 value despite BBAS2 is not present in A12:A13.

I noticed this happened with some of my previous sheets before this, and it got me wondering what causes this? Shouldn't it return as #N/A or #ERROR instead?

I would be glad if someone could give me an explanation of why this happens as well as what can I do to fix it.

Upvotes: 0

Views: 3285

Answers (2)

player0
player0

Reputation: 1

use vlookup:

=ARRAYFORMULA(IFNA(VLOOKUP(A8:A10, A12:E, 5, 0)))

0

Upvotes: 0

ProfoundlyOblivious
ProfoundlyOblivious

Reputation: 1485

Applies to Google Sheets and Microsoft Excel

There are three arguments in MATCH

  • Lookup value
  • Lookup array
  • Match type

MATCH(Lookup Value, Lookup Array, [Match Type])

Match type is optional and accepts one of three values

  • 1 = exact or next smallest (default)
  • 0 = exact match
  • -1 = exact or next largest

You've omitted the match type in your formula...

MATCH(A9,$A$12:$A$13)

This is the same as using the the default match type...

MATCH(A9,$A$12:$A$13,1)

The exact match was not found so your formula returned the nearest value that is less than the lookup value

Using 0 forces the exact match

MATCH(A9,$A$12:$A$13,0)

In your example, this results in the expected error

=INDEX($E$12:$E$13,MATCH(A9,$A$12:$A$13,0))

Upvotes: 1

Related Questions