LOOKUP Formula Question

[chrismdusa]

Lets say I have a sheet with cells A1 & down having dates starting with 1/1/2001 and continuing down with A2 having 1/2/2001, A3 having 1/3/2001 and so on up until today's date.

Then in B1 & down I have any random number between 1 and 60 all the way down until coming to today's date in column A...the catch is that in column B, each number 1 through 60 can appear in multiple cells...completely random in no particular order.

I suppose I would use LOOKUP, but I just can't get it right, but...In C1 I want a formula that will tell me the latest date in column A that a given number appears in column B. Remember, any of the numbers 1 - 60 may appear in multiple column B cells.

Can a formula like this be done? Thanks in advance for the help!

Chris

 

[unnilennium78]

hope that this is what u looking for.......

=LOOKUP(TODAY(),A:A,B:B)

this will only work if you are looking for today's date & the date on your system is correct.....

CHEERS

 

[chrismdusa]

Thanks, but that's not it. For example I might be looking for the latest date where number 22 appeared. It could be any of the dates. Thanks though.

 

[PaddyD]

something like:

max(if(b1:b10=3,a1:a10))

or

max(if(b1:b10=c1,a1:a10))



..entered with control + shift + enter, not just enter

 

[ExcelChampion]

This assumes the value to lookup is in D1:

=INDEX(A:A,LARGE(IF(B1:B226=D1,B1:B226*ROW(1:226)/D1),1))

Must be confrmed w/ctrl+Shift+Enter

For a data set that will grow:

=INDEX(A:A,LARGE(IF(B1:INDEX(B:B,MATCH(9.9999999999999E+307,B:B))=D1,B1:INDEX(B:B,MATCH(9.9999999999999E+307,B:B))*ROW(B1:INDEX(B:B,MATCH(9.9999999999999E+307,B:B)))/D1),1))

Ctrl+Shift+Enter

 

[jim may]

Here's another (in a standard module) paste in:

Sub foo()
mValue = Range("C1").Value 'Value in Col B your searching for
lrow = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & lrow).Select
For i = lrow To 1 Step -1
If Cells(i, 2).Value = mValue Then
mDate = Cells(i, 2).Offset(0, -1).Value
MsgBox "The Date is " & mDate
Exit Sub
End If
Next i
End Sub

 

[chrismdusa]

Thanks for the help. I was a little sick yesterday so I wasn't on the site. Going to try the suggestions now. Thanks!

 

[chrismdusa]

Got everything working perfect. Thanks all!