Excel Magic Trick 1116

Lookup Every Third Item or Lookup Every "Nth" Item, 3 Formula Methods


(00:37) INDEX and MATCH when we have a two fields (columns): Sample Number and Number To Be Averaged. Use MATCH to lookup first occurrence of each sample number with Exact Match, which only finds first item when there are duplicates.
(02:14) INDEX function and a “Fill Handle Copy” trick to increment the position of every third item.
(03:19) Dynamic Formula to look up Every “Nth” item using INDEX function and a formula element that creates sequential numbers based on a start position and the increment between lookup values. ROWS function for formula element that will create sequential numbers like: 1, 4, 7…


Download “Excel Magic Trick 1101-1120” Excel-Magic-Trick-1101-1120.zip – Downloaded 305 times – 2 MB

Be the first to comment

Leave a Reply