1

I am using this formula within a Google Sheets cell to search and match the barcode I am looking for:

=INDEX(UPCCodes!B:B,MATCH(N2,UPCCodes!A:A,0))

The reason this isn't working, is because N2 is another formula:

=RIGHT(Extract!C5,LEN(Extract!C5)-FIND(":",Extract!C5))

I know if I convert the N2 formula to value, then it will work. For example, this works just fine:

=INDEX(UPCCodes!B:B,MATCH("TX4011SBR",UPCCodes!A:A,0))

This does add an extra step in which I need to go in and convert formula result to value. And I was wondering if there was a more automated option of converting formula result to value?

enter image description here

enter image description here

If I added the value manually, it works properly:

=INDEX(UPCCodes!B:B,MATCH("2002xlb",UPCCodes!A:A,0))
  • 1
    @robbie777 : If the N2 formula were correctly written it should work fine. Please provide sample inputd data and the expected result. – Rubén Apr 5 '17 at 21:27
2

You have an extra white space in the result of your formula, it's quite easy to miss. Always "sanitize" your input to lookups by enclosing the call to the text processing formula in a TRIM() function like so:

=INDEX(UPCCodes!B:B,MATCH(TRIM(N2),UPCCodes!A:A,0))

See the error message on your screenshot there's a white space between the quote mark and the first digit!

did not find value ' 2002XLB' ... Etc 

As for your question, compound functions should work in any spreadsheet, as long as they return a value of the type expected by the enclosing function.

That's the reason you're getting a lookup error, (did not find value ) the MATCH() function is working fine, except there is no index ' 2002XLB' in your table. ' 2002XLB' != '2002XLB'

If MATCH() were getting an unexpected value type, you'd get a N/A error. The error message is always a good hint into what went wrong :)

Also, this is a very common scraping error. Get in the habit of sanitizing and normalizing strings before a lookup (trimming stray whitespace, making the key ALL CAPS, (helps with case sensitivity) validating with the info functions before lookups (isnumber(), isemail(), isnull() and friends )

  • Thank you so much! This definitely fixed it :D I'll be sure to keep an eye out now for extra space when scraping! – robbie777 Apr 17 '17 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.