Vlookup and min formula

[Scott@CW]

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?

 

[ryanb.]

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.

 

[Dave Peterson]

=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()))

 

[Scott@CW]

both suggestions worked great. Thank you everyone!