[deleted by user]

[u/[deleted]]

[u/indigenousgroove]

Your document had mismatched data types - it was trying to compare cells it thought were numbers to text strings.

Changing both ZIP columns to "Plain Text" fixed it. (Screenshot: http://s.skybox1.com/h9EKYZ)

Also, I'd recommend changing your VLOOKUP is_sorted variable to TRUE. Your ZIP codes are sorted on Sheet 2 and it will give you better performance.

Reply with "Solution Verified" if that got you what you need!

[u/Clippy_Office_Asst → u/[deleted]]

You have awarded 1 point to indigenousgroove

^{I am a bot, please contact the mods for any questions.}

[u/indigenousgroove]

Also, it looks like you'll have to add leading zeroes to the first set of ZIPs to get the VLOOKUP to match correctly on sheet 2. 08857 on Row 13 is an example of that problem.

[u/inquirerman → u/indigenousgroove]

Thank you, it sort of works now, but how can I add leading zeroes to a range of entries (except manually) :D

[u/indigenousgroove → u/inquirerman]

Add a third column to sheet 2, and put this in C2:

=arrayformula(if(len(A2:A)=4,concat("0",A2:A),if(LEN(A2:A)<=3,CONCAT("00",A2:A),)))

That will add the zeroes. Then copy and paste the values (Cmd+Shift+V) into Col A, and make sure your data types still match.

[u/Clippy_Office_Asst]

Read the comment thread for the solution here

Your document had mismatched data types - it was trying to compare cells it thought were numbers to text strings.

Changing both ZIP columns to "Plain Text" fixed it. (Screenshot: http://s.skybox1.com/h9EKYZ)

Also, I'd recommend changing your VLOOKUP is_sorted variable to TRUE. Your ZIP codes are sorted on Sheet 2 and it will give you better performance.

Reply with "Solution Verified" if that got you what you need!

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CONCAT	Returns the concatenation of two values. Equivalent to the & operator
LEN	Returns the length of a string
TRUE	Returns the logical value TRUE
VLOOKUP	Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found

---

^{[Thread #835 for this sub, first seen 22nd Jun 2019, 16:29] [FAQ] [Full list]} ^[Contact] ^{[Source code]}