Formula/Format Question

Options

I am creating a reclass template that links (look up) the A/C from a Chart of accounts table. The account column is derived from another formula that looks at the GL Account Column and pulls the 5 digit A/C #. When I test the link lookup formula, it returns #No Match. If I take the formula out of the Account Number column the Linked formula works. What am I missing here?

Account Number is derived as following:

= MID([G/L ACCOUNT]@row,9,5)

How do I use INDEX/MATCH to return the GL Account Name from the {COA Range 1} external link?

Please and Thank you!

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Options

    Is the first column in the {COA Range 1} the Account Number? VLOOKUP always uses the first column as its matching criteria.

    If you are just pulling information from a single column then INDEX/MATCH might be a better choice.

  • Leah_Driven Brands1
    Options

    I've tried INDEX/MATCH but I'm getting #UNPARSEABLE

    =INDEX({COA Range 1}, MATCH(MID([G/L ACCOUNT]@row, 9, 5)1 GL Account Name))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Leah_Driven Brands1

    When using LEFT, RIGHT, or MID functions, the result is automatically a textstring- even if it looks like a number. I'm guessing that your table of GL Accounts are actual numbers. A mismatch in textstrings and numbers result in NoMatches

    Try this. I will refer to the columns that you will need to create cross sheet references for, if you haven't already. Replace my references by inserting your real ones. I also assumed your GL Account Name and GL Account Number were two different columns- not that they would have to be.

    =INDEX({source sheet GL Account Name column},MATCH(VALUE(MID([G/L ACCOUNT]@row, 9, 5)), {source sheet GL Account Number column}, 0))

    The zero is part of the Match function. You need that in there.

    Will this work for you?
    Kelly

  • Leah_Driven Brands1
    Options

    Hi Kelly Moore!

    Your suggestions got me closer. Here's where I am now.

    =INDEX({COA Description 3}@row,(MATCH(VALUE(MID([G/L ACCOUNT]@row) 9, 5), {COA GL Account}@row,0)))

    I'm still getting #UNPARSEABLE. 😫

    Any ideas?

    Thanks,

    Leah

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!