Searching for a matching value from one workbook to another.

Hey thanks a lot for your help, though that doesn't seem to be working.

I have found that I can get what I want with a VLOOKUP whereby I search for the value that matches my original cell and then return the value of the cell next to it.

HOWEVER, this does not seem to work in reverse. The worksheet that has the original values contains multiple occurrences of the same value. For example:

1B1 12-Apr-10
1B2 12-Apr-10
1B4 12-Apr-10
3B4 15-Apr-10
1B1 15-Apr-10
12B3 19-Apr-10
1B1 23-Apr-10

I need to return the value that corresponds to the last, most recent occurrence of 1B1. In this case it would be "23-Apr-10". VLOOKUP seems to always search top down and returns "12-Apr-10". I haven't found much online regarding the LOOKUP function - at least that pertains to Excel 2010 (Microsoft's own support page makes no mention of it). I wonder if INDEX and MATCH might serve the purpose, but again, I'm not sure if I can search only 1 column, and return the position of only the last occurrence of the value to be searched for.

Any idea how to make this work?

Ok I've figured out something that will work.

First step is to the determine the row number of the last occurrence of a particular value. Let's use my example from above...

This is my primary worksheet (in the same workbook), called "Site Visits.xls"

1B1 12-Apr-10
1B2 12-Apr-10
1B4 12-Apr-10
3B4 15-Apr-10
1B1 15-Apr-10
12B3 19-Apr-10
1B1 23-Apr-10

In my secondary worksheet, "Visit Schedule." I want to know the last time a particular site was visited. I have a row for each "site", but it only appears once. So there is only one row for 1B1, even though it appears several times in the "Site Visits" file"

So let's say that my first entry in "Visit Schedule" is 1B1, in cell A1. In cell A2 I enter the following formula:

=MAX(IF('Site Visits'!A1:A100=A1,ROW('Site Visits'!A1:A100)))

IMPORTANT: You have to hit "Ctrl-Shift-Enter" rather than just enter, which makes this an array formula. I don't really know what that means, other than it works. The resulting formula will have { } brackets around it and will look like this: {=MAX(IF('Site Visits'!C8:C2860=A7,ROW('Site Visits'!C8:C2860)))}. However as soon as you select the cell, the brackets are no longer visible. You MUST hit "Ctrl-Shift-Enter" any time you select the cell contents or the formula will revert to an error state.

Ok, that returns the row number of the very last time the value contained in cell A1 occurs in worksheet "Site Visits". In this example, that value will be "7" since that is the row that last entry of "1B1" is in. That doesn't do a whole lot as it is, but thankfully there is the INDEX function.

In cell A3 of "Visit Schedule" I enter this formula:

=INDEX('Site Visits'!$A:$B,'Visit Schedule'!A1,2)

This takes the value obtained in cell A1 and looks for that row number in the worksheet "Site Visits", within the column range specified (A to B). Then it goes back to the worksheet "Visit Schedule" and returns the value in the 2nd column from the original one specified (which was column A). In this situation, the value returned is "23-Apr-10", which is in the second column from the 7th row.