Vlookup won't do the job, this might be a INDEX, MATCH combination?

[krodriguez]

Hi, trying to resolve a puzzle here, not sure if doing a combination of match/index will help

I have two worksheets. On worksheet 1 I have a summary for year-to-date numbers with two fields (net sales and gross profit, I do have more but for this example using only two), like this:
-Cell E1 is a drop down box with numerous years and cell B1 is linked to E1, so it can be dynamic.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]December 2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/1/2015[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Net Sales[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gross Profit[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Worksheet 2 have:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]12/1/2012[/TD]
[TD]12/1/2013[/TD]
[TD]12/1/2014[/TD]
[TD]12/1/2015[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Net Sales[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gross Profit[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Problem: How can I pull to sheet1 the results from sheet 2, net sales 400 and GP 400. Keep in mind that when the date on cell E1 (sheet1) changes to let's say to 12/1/2014 data needs to changes to NS 300 and GP 300. Formula needs to be dynamic to whatever date is selected and net sales and gross profit are not in same sequence as sheet1, so formula needs to review what is on column A based on the date selected on sheet1 and bring the values from sheet2

Thanks!

 

[BobUmlas]

=IFERROR(HLOOKUP($E$1,Sheet2!$1:$100,2,FALSE),"")
filldown

 

[Marcelo Branco]

Maybe...

B2 copied down
=INDEX(Sheet2!$A:$Z,MATCH(A2,Sheet2!$A:$A,0),MATCH($E$1,Sheet2!$1:$1,0))

Hope this helps

M.

 

[krodriguez]

Hi Marcelo, I'm getting a #REF error..

 

[krodriguez]

this one worked Sheet1=INDEX(Sheet2!$B$2:$E$3,MATCH(Sheet1!A2,Sheet2!$A$2:$A$3,0),MATCH(Sheet1!$E$1,Sheet2!$B$1:$E$1,0))

thank you.