lookup function

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

Suppose I have two columns, with Column A containing
dates, and column B containing dollar values.

In Cell 3, if I enter the formula =min(amounts), this will
give the correct lowest value in the range amounts.

How would I go about pulling the associated date out of
column A where the lowest value in this case was. I
thought I would do some sort of vlookup but if I lookup on
the dollar value in the minumum formula's cell, then the
date column would have to be to the right of where I found
the minimum value rather than to the left, correct?

So, what sort of formula do I need to use in this case?

Thanks,


Bruce
 
Well, you got me to a good web site, but index wasn't the
solution. His technique for a left lookup was there. I
used it as follows:

=OFFSET(C8,MATCH(G3,C8:C58,0)-1,-1,1,1)

In this case, cell G3 was where the value resided that I
wanted to find. The range I searched in for that value was
c8:c58. Then the -1,-1 is what got it to give the date
which was in column B in this example.

Thanks for the direction. I just found the right street
once I got into town so to speak.
 
Try this Bruce

Name the Range "Accounts" Cells B1-B5
Enter dates in Cells A1-A5
Enter Values in Cells B1-B5
Enter =MIN(Amounts) in Cell C1
Enter =INDEX($A$1:$B$5,MATCH(C1,$B$1:$B$5,0),1) in D1

Trevor
 
Back
Top