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.

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.

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.

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.

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.