Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
find & return value
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? |
#2
|
|||
|
|||
Sounds right for VLOOKUP to me. Layout is immaterial.
-- HTH Bob Phillips "Melissa" wrote in message ... 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? |
#3
|
|||
|
|||
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. InsertNameDefine 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 Melissa wrote: 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? |
#4
|
|||
|
|||
Thanks very much Roger!
"Roger Govier" wrote: 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. InsertNameDefine 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 Melissa wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a value in a workbook and return the worksheet name | Excel Discussion (Misc queries) | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
Find within Workbook. | Excel Discussion (Misc queries) | |||
Find first positive value in row 3 and return date from row 1 | Excel Worksheet Functions | |||
UDF and Calculation tree | Links and Linking in Excel |