Vlookup? help.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChuckF - 30 Mar 2006 20:16 GMT

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.

Reply to this Message

Domenic - 30 Mar 2006 20:41 GMT

For the last numerical value, try...

=LOOKUP(9.99999999999999E+307,Range)

Hope this helps!

> I have a spreadsheet where date fields are being entered in columns J
> through Z. (each column represents a "Week ending date"
[quoted text clipped - 5 lines]
>
> Thank you in advance for taking the time to think this through for me.

Reply to this Message

ChuckF - 30 Mar 2006 20:58 GMT

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

Reply to this Message

Domenic - 30 Mar 2006 21:04 GMT

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

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

Hope this helps!

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

Reply to this Message

ChuckF - 30 Mar 2006 21:16 GMT

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.

Reply to this Message

Domenic - 30 Mar 2006 21:38 GMT

Try..

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

Adjust the reference/range accordingly.

Hope this helps!

> I know and I did that. let me prase the question this way.
>
[quoted text clipped - 3 lines]
> I need a formula that looks for data in the 11th column, and if data is
> present to give me that data instead.

Reply to this Message

Sean - 31 Mar 2006 13:26 GMT

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

>I have a spreadsheet where date fields are being entered in columns J
> through Z. (each column represents a "Week ending date"
[quoted text clipped - 5 lines]
>
> Thank you in advance for taking the time to think this through for me.

Reply to this Message

SteveG - 31 Mar 2006 15:26 GMT

You could try this instead of a lookup if your dates from K:Z alway
increase.
I made a table in Sheet2!J1:Z10. I numbered J1:J10 from 1-1
ascending. In K1:Z10 I entered various dates that as I moved to th
next column, the dates got greater than the previous dates. The date
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 o
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 ke
after inputing use Ctrl-Shift-Enter simultaneously. The formula wil
appear with {} brackets around it if you do it correctly, it will loo
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 t
retrieve the data from the last column containing data from the ro
that matched the value in A2. Again, this is only if the rightmos
column of your table for that value is the MAX date within that value'
row. Change the workbook/sheet names as needed.

Does that help?

Stev

Reply to this Message