Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function
I have tried and tried to figure this out by looking at past discussions, but
I keep missing something. I have a table like this that is linked to a table on the internet, so I can't change the format of the table. The Month column is a custom formatted cell showing the year and month for the settling price. I need to make a cell in another sheet that finds the correct month/year and gives me the settle price. Because the table is liked to one on the internet, the month column is changing at least once a month so I need a lookup function to keep finding the values. Month Settle 9-Aug 15.04 9-Sept 15.67 10-Jan 16.71 10-Feb 16.82 11-Jan 16.23 11-Feb 17.85 =LOOKUP("9-Aug",Sheet1!A8:A31,Sheet1!G8:G31) This function gives me a #N/A message. I think it is because excel sees 9-Aug as 8/9/2009. This is fine, but when I try =LOOKUP(8/9/2009,Sheet1!A8:A31,Sheet1!G8:G31) I still get #N/A. It seems like it should be really easy to do, I just can't seem to get the function correct. I have also tried the VLookup function, but I have the same problems. Thanks for your help and my Excel skills are fairly basic so I apologize if this seems very elementary. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function
does the cell also have a time in it, like 8/9/2009 5:45, if you are just
looking for a month, insert a column- =month(a1) or whatever and lookup that. "StacyM" wrote: I have tried and tried to figure this out by looking at past discussions, but I keep missing something. I have a table like this that is linked to a table on the internet, so I can't change the format of the table. The Month column is a custom formatted cell showing the year and month for the settling price. I need to make a cell in another sheet that finds the correct month/year and gives me the settle price. Because the table is liked to one on the internet, the month column is changing at least once a month so I need a lookup function to keep finding the values. Month Settle 9-Aug 15.04 9-Sept 15.67 10-Jan 16.71 10-Feb 16.82 11-Jan 16.23 11-Feb 17.85 =LOOKUP("9-Aug",Sheet1!A8:A31,Sheet1!G8:G31) This function gives me a #N/A message. I think it is because excel sees 9-Aug as 8/9/2009. This is fine, but when I try =LOOKUP(8/9/2009,Sheet1!A8:A31,Sheet1!G8:G31) I still get #N/A. It seems like it should be really easy to do, I just can't seem to get the function correct. I have also tried the VLookup function, but I have the same problems. Thanks for your help and my Excel skills are fairly basic so I apologize if this seems very elementary. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function
Normally Aug-9 would be
8/1/2009 not 8/9/2009 is yours any date in august or is it the first of the month. If it is any date then it gets more difficult. -- HTH... Jim Thomlinson "StacyM" wrote: I have tried and tried to figure this out by looking at past discussions, but I keep missing something. I have a table like this that is linked to a table on the internet, so I can't change the format of the table. The Month column is a custom formatted cell showing the year and month for the settling price. I need to make a cell in another sheet that finds the correct month/year and gives me the settle price. Because the table is liked to one on the internet, the month column is changing at least once a month so I need a lookup function to keep finding the values. Month Settle 9-Aug 15.04 9-Sept 15.67 10-Jan 16.71 10-Feb 16.82 11-Jan 16.23 11-Feb 17.85 =LOOKUP("9-Aug",Sheet1!A8:A31,Sheet1!G8:G31) This function gives me a #N/A message. I think it is because excel sees 9-Aug as 8/9/2009. This is fine, but when I try =LOOKUP(8/9/2009,Sheet1!A8:A31,Sheet1!G8:G31) I still get #N/A. It seems like it should be really easy to do, I just can't seem to get the function correct. I have also tried the VLookup function, but I have the same problems. Thanks for your help and my Excel skills are fairly basic so I apologize if this seems very elementary. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function
Here is first of the month...
=VLOOKUP(DATEVALUE("10/1/2009"), A1:B10, 2, FALSE) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Normally Aug-9 would be 8/1/2009 not 8/9/2009 is yours any date in august or is it the first of the month. If it is any date then it gets more difficult. -- HTH... Jim Thomlinson "StacyM" wrote: I have tried and tried to figure this out by looking at past discussions, but I keep missing something. I have a table like this that is linked to a table on the internet, so I can't change the format of the table. The Month column is a custom formatted cell showing the year and month for the settling price. I need to make a cell in another sheet that finds the correct month/year and gives me the settle price. Because the table is liked to one on the internet, the month column is changing at least once a month so I need a lookup function to keep finding the values. Month Settle 9-Aug 15.04 9-Sept 15.67 10-Jan 16.71 10-Feb 16.82 11-Jan 16.23 11-Feb 17.85 =LOOKUP("9-Aug",Sheet1!A8:A31,Sheet1!G8:G31) This function gives me a #N/A message. I think it is because excel sees 9-Aug as 8/9/2009. This is fine, but when I try =LOOKUP(8/9/2009,Sheet1!A8:A31,Sheet1!G8:G31) I still get #N/A. It seems like it should be really easy to do, I just can't seem to get the function correct. I have also tried the VLookup function, but I have the same problems. Thanks for your help and my Excel skills are fairly basic so I apologize if this seems very elementary. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function
This works! All I had to change was the DATEVALUE part. Thanks so much for
your help and quick replies. Have a great day! "Jim Thomlinson" wrote: Here is first of the month... =VLOOKUP(DATEVALUE("10/1/2009"), A1:B10, 2, FALSE) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Normally Aug-9 would be 8/1/2009 not 8/9/2009 is yours any date in august or is it the first of the month. If it is any date then it gets more difficult. -- HTH... Jim Thomlinson "StacyM" wrote: I have tried and tried to figure this out by looking at past discussions, but I keep missing something. I have a table like this that is linked to a table on the internet, so I can't change the format of the table. The Month column is a custom formatted cell showing the year and month for the settling price. I need to make a cell in another sheet that finds the correct month/year and gives me the settle price. Because the table is liked to one on the internet, the month column is changing at least once a month so I need a lookup function to keep finding the values. Month Settle 9-Aug 15.04 9-Sept 15.67 10-Jan 16.71 10-Feb 16.82 11-Jan 16.23 11-Feb 17.85 =LOOKUP("9-Aug",Sheet1!A8:A31,Sheet1!G8:G31) This function gives me a #N/A message. I think it is because excel sees 9-Aug as 8/9/2009. This is fine, but when I try =LOOKUP(8/9/2009,Sheet1!A8:A31,Sheet1!G8:G31) I still get #N/A. It seems like it should be really easy to do, I just can't seem to get the function correct. I have also tried the VLookup function, but I have the same problems. Thanks for your help and my Excel skills are fairly basic so I apologize if this seems very elementary. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Lookup function and Sum function | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |