INDEX MATCH Formula Help

[N_Lamech]

Hi,
Im have an issue to which i believe the answer is an Index Match formula........which i cannot work out how to do!

I have a spreadsheet with 2 sheets. The first is the master set of data, the second contains a selection of data from sheet 1 column A 'Name' to which I need to output the start and end dates.
I have tried to show the example below. The required result is displayed in Sheet 2 table below. For cell B2 I need to search 'Sheet 1 Name=Bob & Task=Copy' then output the startdate.
For cell C2 'Sheet 1 Name=Bob & Task=Check' then output the enddate

Sheet 1
A B C D
NAME TASK STARTDATE ENDDATE
Bob Copy 10/01/12
Bob Check 15/01/12

Sheet 2
A B C
NAME STARTDATE ENDDATE
Bob 10/01/12 15/01/12

Any help is appreciated.

[rvasquez]

Hello there,

Try the following:
In cell A2 of worksheet Sheet2 Type Bob

In cell B2 on worksheet Sheet2, type the following formula. Be sure to press Ctrl+Shift+Enter instead of just enter when exiting the cell after entering in the formula. If you have accepted it correctly, Excel will put brackets around the formula.

Please Login or Register  to view this content.

In cell C2 on worksheet Sheet2, type the following formula. Be sure to press Ctrl+Shift+Enter instead of just enter when exiting the cell after entering in the formula. If you have accepted it correctly, Excel will put brackets around the formula.

Please Login or Register  to view this content.

Let me know if this works for you.

[jason.b75]

Is Sheet 1 sorted in any kind of order?

The array formula that rvasquez has suggested will produce the results you want, but will be slow to calculate, if you're going to use that method I would strongly advise using an adequate sized range rather than full columns.

[martindwilson]

you never said if there could be something in between copy and check
if there is then in sheet 2 b2 these two normally entered formulas
=INDEX(Sheet1!$C$2:$C$1000,MATCH(A2&"copy",INDEX(Sheet1!$A$2:$A$1000&Sheet1!$B$2:$B$1000,0),))
in c2
=INDEX(Sheet1!$D$2:$D$1000,MATCH(A2&"check",INDEX(Sheet1!$A$2:$A$1000&Sheet1!$B$2:$B$1000,0),))