I have a spreadsheet where date fields are being entered in columns J
through Z. (each column represents a "Week ending date"

I am creating a spreadsheet where I would like the date of the last
(furthest right) column is entered. So if J6 has a date of 2/26/2006,
and K6 has a date of 3/5/2006, I want to auto populate 3/5/2006. But
once something is entered in L6, I want it to populate that.

Thank you in advance for taking the time to think this through for me.

For the last numerical value, try...

=LOOKUP(9.99999999999999E+307,Range)

Hope this helps!

In article .com,
"ChuckF" wrote:

I have a spreadsheet where date fields are being entered in columns J
through Z. (each column represents a "Week ending date"

I am creating a spreadsheet where I would like the date of the last
(furthest right) column is entered. So if J6 has a date of 2/26/2006,
and K6 has a date of 3/5/2006, I want to auto populate 3/5/2006. But
once something is entered in L6, I want it to populate that.

Thank you in advance for taking the time to think this through for me.

Maybe I don't understand your advise, but this didn't work.
Any other thoughts?

You need to replace 'Range' with your actual range of cells. For
example...

=LOOKUP(9.99999999999999E+307,J6:Z6)

Hope this helps!

In article . com,
"ChuckF" wrote:

Maybe I don't understand your advise, but this didn't work.
Any other thoughts?

I know and I did that. let me prase the question this way.

I currently have a vlookup which functions properly.
=VLOOKUP(A2,'[BOA Tour Scheduler (MASTER).xls]Store Export'!$5:$736,10)

I need a formula that looks for data in the 11th column, and if data is
present to give me that data instead.

Try..

=LOOKUP(9.99999999999999E+307,INDEX($J$5:$Z$376,MA TCH(A2,$A$5:$A$736,0),0
))

Adjust the reference/range accordingly.

Hope this helps!

In article .com,
"ChuckF" wrote:

I know and I did that. let me prase the question this way.

I currently have a vlookup which functions properly.
=VLOOKUP(A2,'[BOA Tour Scheduler (MASTER).xls]Store Export'!$5:$736,10)

I need a formula that looks for data in the 11th column, and if data is
present to give me that data instead.

Hi,

Not sure exactly what you want but maybe it is this:

Instead of
=VLOOKUP(A2,'[BOA Tour Scheduler (MASTER).xls]Store Export'!$5:$736,10)
Try
=VLOOKUP(A2,'[BOA Tour Scheduler (MASTER).xls]Store Export'!$5:$736,A5)
You will need a formula in cell A5 that will tell you what column number you
lookup function should be looking up in.
For example if your dates are in cells J6 to N6
In cell A5 put the formula "=MATCH(MAX(J6:N6),J6:K6)"
This will return the lookup column number of the latest date

Once you have this you can put it all in one formula if you want. Substitute
the formula in A5 for the reference to A5 in your lookup formula.
=VLOOKUP(A2,'[BOA Tour Scheduler (MASTER).xls]Store
Export'!$5:$736,MATCH(MAX(J6:N6),J6:K6))

Hope this helps.

Sean



"ChuckF" wrote in message
oups.com...
I have a spreadsheet where date fields are being entered in columns J
through Z. (each column represents a "Week ending date"

I am creating a spreadsheet where I would like the date of the last
(furthest right) column is entered. So if J6 has a date of 2/26/2006,
and K6 has a date of 3/5/2006, I want to auto populate 3/5/2006. But
once something is entered in L6, I want it to populate that.

Thank you in advance for taking the time to think this through for me.

You could try this instead of a lookup if your dates from K:Z always
increase.
I made a table in Sheet2!J1:Z10. I numbered J1:J10 from 1-10
ascending. In K1:Z10 I entered various dates that as I moved to the
next column, the dates got greater than the previous dates. The dates
stopped at different column numbers throughout the table. In Sheet1!
A2 I entered my lookup value (1-10) for column J in Sheet!2. In B2 of
Sheet!1 I entered the following formula.


=MAX(IF(Sheet2!J1:J10=Sheet1!A2,Sheet2!K1:Z10))

This is an array formula so rather than just hitting the enter key
after inputing use Ctrl-Shift-Enter simultaneously. The formula will
appear with {} brackets around it if you do it correctly, it will look
like.

{=MAX(IF(Sheet2!J1:J10=Sheet1!A2,Sheet2!K1:Z10))}

Using a value from 1-10 as my lookup value in Sheet!1A2, I was able to
retrieve the data from the last column containing data from the row
that matched the value in A2. Again, this is only if the rightmost
column of your table for that value is the MAX date within that value's
row. Change the workbook/sheet names as needed.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=528241