Match or Index or Lookup HELP!

[Lou]

Hi,

I have worksheet 'B' that contains dates in the column A and data in
columns B through M. The columns B:M are numbered 1 through 12 at the
top. The dates go in ascending order, the reason is too long to
mention here.

Now, in worksheet 'A' I also have the dates in ascending order in
column A and column B contains formulas that return a number, 1
through 12. These numbers correspond to the columns in worksheet B.
In column C of worksheet A I would like to have the data listed that
corresponds to the same date in worksheet B under the column 1-12
based on the number listed in column B of worksheet A.

Confused? I am.

Essentially, I want the first worksheet to return the proper data from
the second worksheet based on the date and the column listed for that
date.

I tried the '=if(b4=1,B!b4,if(b4=2,B!c4,if(b4=3,B!d4,etc...., but ran
into the 7 nested 'if statement' rule. I would need 12. I fooled
with the lookup functions a little but understand the data has to be
in descending order, plus I really don't understand how to use lookup
formulas to begin with! I also don't really understand match or index
formulas but feel that my solution lies with them.

Any help would be greatly appreciated. Thanks!

Lou

 

[Frank Kabel]

Hi
try the following in C1 on your worksheet A
=INDEX('worksheet B'!$A$1:$M$100,MATCH(A1,'worksheet B'!
$A$1:$A$100,0),MATCH(B1,'worksheet B'!$A$1:$M$1,0))

 

[Lou]

Frank, that did the trick! Exactly what I was looking for, thank you so much!

Lou