Match or Index or Lookup HELP!

  • Thread starter Thread starter Lou
  • Start date Start date
L

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
 
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))
 
Back
Top