CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulavlookup
Polk Technology Solutions
Polk Technology Solutions

Reputation: 31

Vlookup Exact Match Google Sheets

hoping for some help understanding why VLOOKUP is not returning exact text matches and is instead showing partial matches. Below is an example of my data.

As you can see in the screenshots, item R2FLG returns a value of 136,861 which is correct, but item R2FLGCLR returns that same value when it shouldn't return anything because the exact item R2FLGCLR isn't in the VLOOKUP source data.

It seems to match the first part R2FLG and thus give it a value of 136,861. I don't want that. I want it to VLOOKUP only exact text match.

Here is the VLOOKUP formula I'm using.

={"Total Parts Received";IFERROR(ARRAYFORMULA(VLOOKUP(C2:C,'Product/Service Import List'!AB:AC,2)))}

Screenshot of the error

Screenshot of the source data

Upvotes: 2

Views: 2582

Answers (1)

OneInAMillion
OneInAMillion

Reputation: 552

The reason your vlookup is getting a relative match is because "If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned." See documentation for more details: https://support.google.com/docs/answer/3093318?hl=en

You can adjust your formula by adding in the false to the is-sorted criteria

={"Total Parts Received";IFERROR(ARRAYFORMULA(VLOOKUP(C2:C,'Product/Service Import List'!AB:AC,2,false)))}

Good luck!

Upvotes: 4

Related Questions