Can I convert a formula within a formula to a value?

Question

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?

If I added the value manually, it works properly:

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

Answer 1

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 )