find & return value

M

Melissa

My first spreadsheet is a calendar of sorts with date in one column and value
in next. Dates for different months are in different columns, so we have:
Date (Jan)| Value| Date (Feb)| Value| Date (Mar)| Value| etc.

My second spreadsheet references this "calendar" but the layout is not the
same at all (so lookups probably can't work?). In this spreadsheet, I have a
list of various dates from different months. What formula can I use to
return the corresponding value for the date, based on the source info in my
first spreadsheet?
 
R

Roger Govier

Hi Melissa

If I understand you correctly, you have 12 x 2 column blocks on Sheet1, each
block containing up to 31 rows.

I would name these blocks as Data1, Data2 etc.
Insert>Name>Define Name Data1 Refers to Sheet1!$A$1:$B$31

On sheet2, to pick up the price in column B enter in B1
=VLOOKUP(A1,INDIRECT("Data"&MONTH(A1)),2,0)

Regards

Roger Govier
 
M

Melissa

Thanks very much Roger!

Roger Govier said:
Hi Melissa

If I understand you correctly, you have 12 x 2 column blocks on Sheet1, each
block containing up to 31 rows.

I would name these blocks as Data1, Data2 etc.
Insert>Name>Define Name Data1 Refers to Sheet1!$A$1:$B$31

On sheet2, to pick up the price in column B enter in B1
=VLOOKUP(A1,INDIRECT("Data"&MONTH(A1)),2,0)

Regards

Roger Govier
Click to expand...
 
Top