[Solved] 2 way lookup between tables

**joedrummer** · 01-27-2012, 06:29 PM

Hello, I'm having trouble with 2 way lookups in tables.
I have 2 tables
One has expenses and their budgets each month.
The other has different types of spending patterns by month.

I need to multiply the budget by the month's spending percent according to it's Spending Pattern. I have a working formula in one column, but I need to drag it across all the months. I want to use a formula that I can drag across months, as we have several other tables of data. I always have problems dragging formulas across months when I use tables. Am I missing a way to use absolute and relative references between tables and columns??

Also, I'm using Excel 2010

Thanks for the help! I hope I was able to explain my problem correctly!
Joe

**JieJenn** · 01-27-2012, 06:38 PM

in G7 copy and paste, drag across and down

=INDEX($B$2:$N$4,MATCH($C7,$A$2:$A$4,0),MATCH(G$6,$B$1:$N$1,0))*$B7

**joedrummer** · 01-27-2012, 07:40 PM

Thanks JieJenn, this is close to what I want. I've updated my spreadsheet using your formula to get the exact numbers I need. I would still like to know if there a way to do this index using the table structured references? I ask because we have lots of different budgets for different areas of the company. Also, when working with tables like these, I very frequently want to drag formulas (with the table's structured references) across columns, and it always treats the columns like relative references, but I want them to be absolute.

**JieJenn** · 01-28-2012, 02:49 AM

Unfortunately, I don't know how to lock the reference when it is a table reference.

**joedrummer** · 01-28-2012, 11:49 AM

Well I've found out why I was having the problem. In table references, if you drag across, all references are treated as relative and shift with the drag. If you copy and paste each column individually, they are treated as absolute. I was having problems because I want some to stay absolute and the date to shift.
Thanks for your help