MATCH(A$4&"*",B6:B30,-1)

I am assuming A4 contains a date and column B contains dates followed by some other text, thus the reason for the wildcard in the formula. If that is the case, can you make a column with just the dates that can be searched without the wildcard? Maybe with a formula like =LEFT(B,10) ? Or, if your dates are not consistent in length (some might be MM/DD/YYYY and others MM/DD/YY, or whatever), you could write a more sophisticated text function to pull the dates out.

I am also assuming by "lowest" you really mean "smallest value greater than or equal to the value you are searching for" (and similarly for "highest"), not the absolute highest and lowest dates in the column.

I notice that using MATCH with the "*" wildcard gives me not necessarily the row I am expecting. I was expecting either the highest or lowest matching row but, when there is more than one match, it seems to evaluate the rest of the string and base its results on those characters. And sometimes it it doesn't find a match at all. I would expect a match for "12*" would include the string "12a" but apparently not.

Wildcards are only available with find value, not find lowest or highest.

Hi atapp,

To clarify even more, could you provide a list of the data in the column being searched by MATCH. The list does not need to be of the actual data, but does need to match that data in type.

Example: a datum of "2020-04-22 log item 1" could be represented by "2020-03-17 shop call", but not by "20200317 shop call" or "March 17, 2020 shop call"

If the actual data would not cause any privacy concerns, that data would be preferred.

By "highest" and "lowest" dates, do you mean "most recent" and "oldest" respectively?

Regards,

Barry

HI atapp,

As you've provided no samples of the actual data being searched in column B, I've used rank numbers within the set of dates listed in column A. The oldest date, January 1 (2020) is ranked 1, the newest on the list, February 24, is ranked 55.

The table contains 60 rows.

Column A, rows 6 to 60, contains a list of dates in random order.

Column B contains a set of numbers indicating the rank of each date from the oldest (55) to newest (1)

The formula shown on the table in in the selected cell, B4. It gets the rank number from A4, searches for that value in B6:B60, and returns the date from column A in the row on which it finds the value in column B.

B3 contains the same formula, with the first cell reference changed to A3

B4: INDEX(A6:A60,MATCH(A4,B6:B60,0))

For your table, replace the numbers in column B of mine with the text values in yours. Enter the text value you are searching for in A4.

Regards,

Barry

PS: It's not necessary to send individual replies to all people responding to your issue. Each of us (including you) gets an email notice containing each message posted in the discussion (except those messages for which we are the author). Your reply to Badunit or to me could have contained responses to the the questions that both or either of us asked. We'd both see them.

B

I am sorry to say that was actually less information than you provided originally and did not answer the questions.