How Can I VLookup depend on Search on table?

[Ahmed Al-Boghdady]

Hello Excel Experts ,

Can you help me in the attached file , which i had a table that contains of the promotion start date , promotion end date , item name and sales as a master data table and i have an empty table which contains of item and sales . then i need find any way to link the empty table with the cell included the date , when i change the date on this , the only item on the promotion period only show on the table its mean i need to show all item its end promotion end date is greater than or equal the date on cell which i linked with the empty table.

Thanks with my appreciate for your support.

[Pete_UK]

Do you mean that you enter a date into the merged cells G1/H1 and you want to see all the Promotion Items that are still running on that date?

Pete

[Ahmed Al-Boghdady]

Yes , Mr. pete , that is exactly my mean

[Pete_UK]

Okay, put this formula in E3:

=IF(G$1="","",IF(B3>G$1,MAX(E$2:E2)+1,"-"))

then copy down to the bottom of your first table. Then put this formula in G3:

=IFERROR(INDEX(C:C,MATCH(ROWS($1:1),$E:$E,0)),"")

Copy it into H3, then copy G3:H3 down to the bottom of your second table.

Then all you need to do is to change the date in G1/H1 and the display will automatically change.

Hope this helps.

Pete

[Ahmed Al-Boghdady]

Many thanks Mr. Pete .. it is worked with me