VLOOKUP from the top this time!

One way would be to use the fourth argument to specify an exact match.

=VLOOKUP(DATE(2012,1,12), A:B, 2, FALSE) finds the exact match from the top.

If you don't need/want the "exact" match, just say so and someone should be able to find another method to meet your requirements.

Hi Mikey,

You may infer that VLOOKUP will 'pull a full subset', but VLOOKUP does not imply that it will do so (and is incapable of inferring anything). My own inference would be that since VLOOKUP is going to return a value to a single cell, it will return only a single value, and that value will be the one corresponding to the first instance of the search value that is found.

"First" depends on direction of search For LOOKUP, and for VLOOKUP with a 'close-match' that direction is from bottom to top of the search-range. Ronnie's experience has determined that an 'exact-match' setting appears to reverse the direction of search appears true in my brief tests, so using that setting would appear to match your desire regarding defining 'first' to mean 'instance closest to row 1.'

Another choice would be to use MATCH and OFFSET in place of VLOOKUP. MATCH appears to also change its direction of search with changes in the final argument, match-type, as can be seen in the results below. The formulas in Summary::B and C, and in Summary::D, E and F are the same except for the match-type set in the VLOOKUP or MATCH function (indicated by the number at the end of the column label).

Summary::B2: =VLOOKUP($A2,Data :: $A:$B,2,)

(omitted=TRUE—close-match, shown in label as "1", FALSE—exact-match, shown in label as "0")

Summary::D2: =OFFSET(Data::$A$1,MATCH($A,Data :: $A,1)-1,1)

But your post implies that a single instance is not what you want to return in your summary table. Perhaps a more complete specification of the results you are looking for would help determin a solution to the actual issue.

Regards,

Barry

PS: "instatiated"? Mistyped? What word was intended?

Why VLOOKUP and LOOKUP search from bottom to top is a mystery to me as well. I was sure I had read the description in the documentation somewhere, but a search for that while writing the post above was unsuccessful. It's posible I read the information in another post here or elsewhere. I can't recall anything addressing the Why question, though.

Ronnie's discovery of the reversal in apparent search direction when VLOOKUP searches for an exact-match was also a surprise to me. I wonder if that piece of the puzzle might be useful in your case where you are looking for two rows with the same date. The match-type argument in VLOOKUP could be made to alternate from row to row is set by a pair of functions:

=VLOOKUP($A2,Data :: $A:$B,2,ISODD(ROW()))

Formula above is in all cells (except the header cell) in Summary::B

Error triangles are 'couldn't find' errors due to the empty cells in column A.

Might fit your scenario better.

Anyway, this isn't the first time I've had this crisis. I have another table in a different spreadsheet that is having "dating problems," so your suggestions here are making me think of looking at that monster again...

Good. Always nice to see a post has been useful beyond the current issue.

PS: "instatiated"? Mistyped? What word was intended?

How embarrassing. I can't even spell a silly word like "instantiated" correctly. Either that, or I couldn't spell correctly that I wasn't hungry (insatiated). I don't know, I apparently wasn't communicating very well on a number of points. It was pretty late last night... 😁

Been then, done that. 😉

Regards,

Barry

Relying on an undocumented feature is risky. The undocumented feature in this case is that an exact match appears to start the search from the top.

A more difficult method but one that does not rely on the direction of search is as follows:

Column A is your dates

Cell B2 =DATEVALUE(A)&COUNTIF($A$2:A2,A)

Fill down from B2 to complete column B

Column C = your "other data" column

The search =VLOOKUP(DATE(2012,1,12)&1,B:C,2)

Hide column B after setting everything up.

What it does is create in column B a string that is the date + a number indicating how many times that particular date has occurred so far. First occurrence will be a 1, second will be a 2, etc.

I used DATEVALUE in the column B formula so that it would ensure the proper format of the date when converted to a string. The date in Column A can be formatted some other way without affecting the results. The COUNTIF doesn't care how the date in column A is formatted, you can have one row as Jan 12, 2012 and another as 12-Jan-2012, they are equivalent.

You can also search for other occurrences of the same date, if you have a need to.

Badunit writes:

"Relying on an undocumented feature is risky. The undocumented feature in this case is that an exact match appears to start the search from the top."

True, but in this case there may also be risk in 'relying on a documented feature'.

Consider the results in the last three columns of my previous post (copied below), where the difference in the MATCH function was in the "match-type" argument, in the light of this statement from the iWork Formulas and Functions User Guide article on MATCH (emphasis added):

"Cell numbering starts with 1 at the top or left cell for vertical and horizontal ranges,

respectively. Searches are performed top-to-bottom or left-to-right."

That said, your use on an index column is probably the right direction in which to head.

Regards,

Barry