Google Sheets – How to Convert Formula to Value

google sheets

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))

Best Answer

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 )

Related Solutions

Google-sheets – Apply Formula to Newly Inserted Row

The solution below involves a very elegant and generally useful technique popularized by ahab, a Google Docs expert; I suppose he invented it. It uses matrix multiplication.

Move the label "Starting" to the last cell of the Description column, column D.

Move the starting balance to the last cell of the Amount column, column E.

Insert a new blank row 2. This row will be reserved and should be left blank except for the following formula, so you might want to freeze it along with row 1.

Put the following formula in cell F2.

= ArrayFormula( if( isnumber(E2:E)
      ;
    mmult(
      transpose(E2:E) * (row(E2:E) <= transpose( row(E2:E) ) )
        ;
      sign( row(E2:E) )
    )
      ;
) )

Any row having a transaction amount in column E now has a corresponding running balance in column F. The running balance has the desired property that the initial balance is in the last row of column F and subsequent balances read up column F.

If, instead, you want your starting balance in the first (data) row of column F (cell F2) and subsequent balances to read down the column, change <= to >=.

Google Sheets – Using INDEX-MATCH for Nested Lookups

If I'm understanding correctly:

=INDEX(Employer!B:B;MATCH(INDEX(StudentandEmployer!D:D;MATCH(A4;StudentandEmployer!A:A;0));Employer!A:A;0))

Although I think in this situation using VLOOKUP instead of INDEX(MATCH()) might be a bit more straightforward:

=VLOOKUP(VLOOKUP(A4;StudentandEmployer!A:D;4;0);Employer!A:B;2;0)

Best Answer

Related Solutions

Google-sheets – Apply Formula to Newly Inserted Row

Google Sheets – Using INDEX-MATCH for Nested Lookups

Related Topic