Index and Match Issue - Microsoft Community

i have workbook multiple tabs.  first tab called master includes data in a1:aw200, customer name in column x, (column number 24).  column contains customer number , has heading of custno. master table given name of table1 excel.  named master.

i have tab contains subset of data in master.  tab called table2 excel, , named customer.  column contains customer number heading of custno, , column b contains customer last name, , has heading of ln.

i using combination of index , match using custno pull customer's last name master.  index , match formula works fine , shown below.

=index(master,match([@[custno]],table1[custno],0),24)

the problem if insert columns in master, column 24 column 25, butthe column number in formula remains 24, , doesn't change 25 reflect change.

how change formula column number changes?

hi rick,

if in table1 first column (a) named custno , x column ln, instead of 24 may use

column(table1[ln])-column(table1[custno])+1 

Office / Excel / Windows 8 / Office 2010