One of our MVPs brought a tutorial on ExcelCampus (http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/) to my attention, and I thought it was so cool we should probably share it with everyone. When you select a column of data in a table to create a reference in your formulas, it automatically creates a structured reference for you – say the table header is Region in Table1, as shown below,
the reference would read
This is all well and good until you try to drag your formula to the right. Assuming you WANT the reference to move to the right as well. But suppose you don’t… =LOOKUP($O$3,Table1[Region],Table1[Quarter1]) becomes =LOOKUP($O$3,Table1[Quarter1],Table1[Quarter2]), which really doesn’t get you what you want. So here’s the trick to get around that. Double the field name to make it a range like this: =LOOKUP($O$3,Table1[[Region]:[Region]],Table1[Quarter1]). Then when you copy over, you get the absolute column for Region: =LOOKUP($O$3,Table1[[Region]:[Region]],Table1[Quarter2]).
Cool, huh? Try it, you’ll like it!