I have a table in Excel, which for simplicity sake we will define as:
Order# | Date | Price |
123 | 1-1-2013 | 100 |
135 | 1-2-2013 | 150 |
145 | 2-4-2013 | 100 |
145 | 2-5-2013 | 100 |
123 | 3-5-2013 | 250 |
In this table Order# is a recurring value with a different Date and Price each time (i.e. installment billing). The table is sorted by Date oldest to newest. I am unable to modify or rearrange the contents of this table in any way.
I have a separate list with a partial-sample of Order#, for which I need to query against the main table and return the Date value for the last matching instance of the Order#. For example, if the partial-sample of orders was (123, 135), then the table would look like this:
Order# | Last_Match_Date |
123 | 3-5-2013 |
135 | 1-2-2013 |
I tried using the vlookup(Order#,TableArray,DateColumn,false)
function, but it returns the first-matching date value (oldest date), and I need the last-matching (newest date).