SOLVED

insert product into a timeline in a separate tab

%3CLINGO-SUB%20id%3D%22lingo-sub-2504626%22%20slang%3D%22en-US%22%3Einsert%20product%20into%20a%20timeline%20in%20a%20separate%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2504626%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%20I%20am%20fairly%20new%20to%20excel%20and%20like%20with%20most%20things%2C%20I%20like%20to%20jump%20right%20in.%20I%20have%20identical%20product%20schedules%20in%20tab%201%20and%20tab%202%20(they%20are%20just%20formatted%20differently).%20I%20would%20like%20to%20auto%20populate%20the%20product%20name%20in%20the%20product%20column%20of%20tab%203%20based%20on%20the%20beginning%20and%20end%20dates%20of%20the%20product%20campaign.%3CBR%20%2F%3EI'd%20really%20appreciate%20any%20and%20all%20help.%20I%20have%20tried%20nesting%20IF%20formulas%2C%20VLOOKUP%2C%20SUMPRODUCT%2C%20and%20others%20but%20to%20no%20avail.%20These%20functions%20will%20probably%20work%20but%20I%20may%20not%20applying%20them%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2504626%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2505625%22%20slang%3D%22en-US%22%3ERe%3A%20insert%20product%20into%20a%20timeline%20in%20a%20separate%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2505625%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089440%22%20target%3D%22_blank%22%3E%40SteveCW%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Product%20column%20contains%20data%20validation%20that%20refers%20to%20an%20external%20workbook.%20I'd%20remove%20this.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%26nbsp%3B%20C2%20on%20the%20'3'%20sheet%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(Table34%5Bproduct%5D%2CMATCH(1%2CINDEX((Table34%5Bstart%5D%26lt%3B%3D%5B%40Date%5D)*(Table34%5Bend%5D%26gt%3B%3D%5B%40Date%5D)%2C)%2C0))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20should%20automatically%20fill%20the%20entire%20Product%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi everyone! I am fairly new to excel and like with most things, I like to jump right in. I have identical product schedules in tab 1 and tab 2 (they are just formatted differently). I would like to auto populate the product name in the product column of tab 3 based on the beginning and end dates of the product campaign.
I'd really appreciate any and all help. I have tried nesting IF formulas, VLOOKUP, SUMPRODUCT, and others but to no avail. These functions will probably work but I may not applying them correctly.

2 Replies
best response confirmed by SteveCW (New Contributor)
Solution

@SteveCW 

The Product column contains data validation that refers to an external workbook. I'd remove this.

Enter the following formula in  C2 on the '3' sheet:

 

=IFERROR(INDEX(Table34[product],MATCH(1,INDEX((Table34[start]<=[@Date])*(Table34[end]>=[@Date]),),0)),"")

 

Excel should automatically fill the entire Product column.

Thanks Hans this works perfectly!