Table Formula to automatically fill in when adding a row

I created a lookup formula on a Table.   It is pulling from another table to populate the field.  The formula works fine however, I would like to populate on the next row.  What am I doing wrong.  I have attached a sample file.

=INDEX('Bank Info'!$B$7:$E$10,MATCH([@[Account Number]],TblBankAccount[Acct number],0),4)

Thanks, Eric
Index-Match-in-a-Table.xlsm
ekaplan323Asked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
There is an option in Excel Options > Advanced for:

"Extend data range formats and formulas"

Ensure this is enabled.

If the area in question is "defined" as a table, it should do it automatically.

Thanks
Rob H
0
 
NBVCCommented:
Not sure what you mean by
I would like to populate on the next row
.  Can you elaborate?
0
 
etech0Commented:
You can use control-d to copy the formula from the cell above.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
etech0Commented:
Or you could do something like this:

=iferror(INDEX('Bank Info'!$B$7:$E$10,MATCH([@[Account Number]],TblBankAccount[Acct number],0),4),"")

and copy it down to all the cells down to 500 or whatever. It will show blank unless there is a result
0
 
ekaplan323Author Commented:
Rob,

I think this is the issue, can't find the option in Excel 2010 Options Advanced.
0
 
Rob HensonFinance AnalystCommented:
See attached.
Excel-Options.png
0
 
ekaplan323Author Commented:
Solved the problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.