VLookup for table with varying rows.

[ssmith3156]

I have this VLOOKUP for Table3, =IFERROR(VLOOKUP([@Concatenate],'COST TYPE SUMMARY'!$G$2:$H$35,2,FALSE),"0.00"). The rows will vary each month, so I need the formula to auto-populate the new cells when they are added. Is there a way to modify this formula to make that happen?

 

[Jeffrey Mahoney]

There are two ways of doing this. Create a Dynamic named range or turn your Cost Type Summary range into an Excel Table.

Dynamic named range that I named CostType_Tbl:
=OFFSET('Cost Type Summary'!$G$1,1,0,MATCH("zzzzzzzzzzz",'Cost Type Summary'!$G:$G,1)-ROW('Cost Type Summary'!$G$1),2)
Use it like:
=IFERROR(VLOOKUP([@Concatenate],CostType_Tbl,2,FALSE),"0.00")
Managing this requires no extra input from you. This assumes that column G is text based data.

Excel Table named CostTypeTbl
Use it Like:
=IFERROR(VLOOKUP([@Concatenate],CostTypeTbl,2,FALSE),"0.00")
Managing this requires that you resize the table if extra data is added or rows are removed. Sometimes the table will resize larger, but not smaller

 

[ssmith3156]

There are two ways of doing this. Create a Dynamic named range or turn your Cost Type Summary range into an Excel Table.

Neither of those worked for me. But, it could be because I'm still a novice at tables. I have attached screenshots of my original sheets. If you don't mind taking a look, I would greatly appreciate it. Table1 is on another tab (Cost Type Summary). The VLookup formula is in the Rate column of Table3.

 

[Jeffrey Mahoney]

As you can see, I named my table CostTypeTbl and your table name is Table1. So you're either going to have to change your formula so it looks a Table1 or change your table name to CostTypeTbl.

 

[ssmith3156]

As you can see, I named my table CostTypeTbl and your table name is Table1. So you're either going to have to change your formula so it looks a Table1 or change your table name to CostTypeTbl.

This is my original. When I tested the codes you gave me, I did change the table names to match, but it did not work for newly added rows without me manually dragging a cell with the formula to the new rows.

 

[ssmith3156]

As you can see, I named my table CostTypeTbl and your table name is Table1. So you're either going to have to change your formula so it looks a Table1 or change your table name to CostTypeTbl.

Thank you for your help. I was able to solve this by going a different route.

 

[Jeffrey Mahoney]

Thank you for letting me know