MATCH and INDEX formula?

[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.

	A	B	C	D	E
1		December 2015			12/1/2015
2	Net Sales	400
3	Gross Profit	400
4

<tbody>
</tbody>

Worksheet 2 have:

	A	B	C	D	E
1		12/1/2012	12/1/2013	12/1/2014	12/1/2015
2	Net Sales	100	200	300	400
3	Gross Profit	100	200	300	400
4
5

<tbody>
</tbody>

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.

Thanks!

 

[elmer007]

In B2 (of sheet 1): =INDEX(Sheet2!B2:E2, MATCH(E2,Sheet2!B1:E1,0))

 

[krodriguez]

I'm getting a #REF error... I should add that in worksheet 2 Net sales and gross profit are not in order and they don't have the same sequence as worksheet 1. Shouldn't the formula also be looking at column A to grab the right cells?

 

[MARZIOTULLIO]

Hi

Try

B2 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)) Copy down

 

[krodriguez]

@MARZIOTULLIO great this works perfect! thank you

 

[MARZIOTULLIO]

Hi

You are welcome