Index, Match, Offset? Not sure which to use

[Guest]

Here's what I have. I would like to enter a date in one worksheet and based
on that date, find the date in the second worksheet and place a range of data
it into the first. For example:
On worksheet 1 I want to enter a date into cell d1, then I need it to look
for the date in column A on worksheet 2 and when it finds it return a range
of data thats 6 cells up and 2 to the right, 7 high and 10 wide. I'm not
having any luck. I appreaciate any help you can provide.

 

[KL]

Hi,

The following the array you describe, but obviously it can't be viewed as a
result in one cell - you can however manipulate it in some further
operations no problem:

=OFFSET(INDEX(Sheet2!A:A,MATCH(D1,Sheet2!A:A,0)),-6,2,7,10)

Regards,
KL

 

[Guest]

Thank you for your response, I really appreciate it. However, I tried the
formula suggested and I get an #N/A. Should I enter this formula as an array?
Even so, I still get the #N/A error. Regarding your comment on putting the
data in one cell, I don't want it in one cell. I want it to be the same
number of columns ands rows as what it's retrieving. Any other idea?
thanks again.

 

[KL]

Hi,

I tried the
formula suggested and I get an #N/A.

This means that either the date from D1 doesn't exist in range Sheet2!A:A
or otherwise either D1 or Sheet2!A:A contains text looking like date (which
is in principle the same as the former). This error is most probably
returnned by the function MATCH.

Should I enter this formula as an array?

Yes, if you want to select a range 7 rows high and 10 columns wide paste the
formula into the formula bar and the hit Ctrl+Shift+Enter in order to
introduce the formula in all cells at once.

Or to introduce the values individually you could use the following formula
e.g in cell [F9] and copy it 6 rows down and 9 columns left:

=OFFSET(INDEX(Sheet2!$A:$A,MATCH($D$1,Sheet2!$A:$A,0)),-6+ROW()-ROW($9:$9),2+COLUMN()-COLUMN($F:$F))

Also, be warned that both formulas will return the #REF! error if the
searched value is found in row 6 or higher of Sheet2.

Regards,
KL

 

[Guest]

Your are correct. I didn't realize that column A was hidden on sheet2, and I
needed to index column B. thanks.

Hi,

I tried the
formula suggested and I get an #N/A.

This means that either the date from D1 doesn't exist in range Sheet2!A:A
or otherwise either D1 or Sheet2!A:A contains text looking like date (which
is in principle the same as the former). This error is most probably
returnned by the function MATCH.

Should I enter this formula as an array?

Yes, if you want to select a range 7 rows high and 10 columns wide paste the
formula into the formula bar and the hit Ctrl+Shift+Enter in order to
introduce the formula in all cells at once.

Or to introduce the values individually you could use the following formula
e.g in cell [F9] and copy it 6 rows down and 9 columns left:

=OFFSET(INDEX(Sheet2!$A:$A,MATCH($D$1,Sheet2!$A:$A,0)),-6+ROW()-ROW($9:$9),2+COLUMN()-COLUMN($F:$F))

Also, be warned that both formulas will return the #REF! error if the
searched value is found in row 6 or higher of Sheet2.

Regards,
KL