If we replace the "1" for the row with the MATCH formula (yes, you can "nest" formulas inside of other formulas), we get the desired merged cell: OFFSET ($A1,1,1,1,1) would select cell B2 (John Brown). OFFSET($A1, row, col, height, width) using 1 for row, 1 for col,1 for height, and 1 for width would offset from A1 by one row down, one column over for a height and width of one cell. OFFSET will use the index returned by MATCH to pick the correct field. MATCH($A2,Name!$A$2:$A$4,0) will give us a "1" which is the first row of A2:A4.
MATCH will look for an exact match in the data, regardless of sorting.
Free Agile Lean Six Sigma Trainer Trainingįree Excel Tips » Merging Worksheets or Workbooks Merging Excel Worksheets or Workbooks.Animated Lean Six Sigma Video Tutorials.