Vlookup and min formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott@CW - 10 Jan 2008 13:49 GMT

I have a worksheet that has dates in column B and file # in column A. In cell
J12, i have this formula =min(B2:B100), in L12, I have a vlookup to return
the information in column A that matches the date =vlookup(J12,A2:B100,1,0).
I receive a #name error.

Any ideas?

Reply to this Message

ryanb. - 10 Jan 2008 14:49 GMT

VLOOKUP goes left to right. Are you able to move dates to A, and file
names to B? what about having filenames in both A and another column to the
right of the dates column (say in column C enter the formula =A12 in cell
C12... then change the range in your VLOOKUP to B:C)? since your lookup
value is a date returned with the min formula, the date column has to be the
first column in the range argument of a VLOOKUP formula.

HTH,

ryanb.

>I have a worksheet that has dates in column B and file # in column A. In
>cell
[quoted text clipped - 4 lines]
>
> Any ideas?

Reply to this Message

Dave Peterson - 10 Jan 2008 14:55 GMT

=vlookup() expects the key column to match to be the leftmost column of the
lookup range.

But you can use a different formula when that key column is not the leftmost
column:

=index(a2:a100,match(j12,b2:b100,0))
or even
=index(a2:a100,match(min(b2:b100),b2:b100,0))

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

> I have a worksheet that has dates in column B and file # in column A. In cell
> J12, i have this formula =min(B2:B100), in L12, I have a vlookup to return
> the information in column A that matches the date =vlookup(J12,A2:B100,1,0).
> I receive a #name error.
>
> Any ideas?

Signature

Dave Peterson

Reply to this Message

Scott@CW - 10 Jan 2008 15:02 GMT

both suggestions worked great. Thank you everyone!

> =vlookup() expects the key column to match to be the leftmost column of the
> lookup range.
[quoted text clipped - 17 lines]
> >
> > Any ideas?

Reply to this Message