Formula/Format Question

Leah_Driven Brands1

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!

Devin Lee

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

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

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

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