# LOOKUP Formula Question

#### chrismdusa

##### Board Regular
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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### unnilennium78

##### New Member
hope that this is what u looking for.......

Code:
=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

##### Board Regular
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.

##### MrExcel MVP
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

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
Got everything working perfect. Thanks all!

Replies
1
Views
866
Replies
6
Views
258
Replies
6
Views
213
Replies
1
Views
277
Replies
18
Views
317

1,190,608
Messages
5,981,908
Members
439,743
Latest member
KatieO

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back