2

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).

1 Answer 1

2

Under the Last_Match_Date column enter this array formula 1 and copy it down:

=MAX(IF(order_numbers=A2,dates,""))

Where:

  • order_numbers refers to the first column in your source data (Order#)
  • dates refers to the second column (Date)
  • A2 is the order number you're trying to look up in the separate list

The formula will return the latest / newest date even if your source data isn't sorted.

Example:

In the example below, I replaced order_numbers with $A$1:$A$6 & dates with $B$1:$B$6.

enter image description here

--

1 Press Ctrl + Shift + Enter after entering the formula

3
  • This partially works. If I make each individual cell an array, then it pulls the correct date. If I don't and instead make the entire table an array, then it only uses the first order number (A13) as the comparison. How can I make this a true array function so I don't need to manually set each row as an array? I tried =MAX(IF(A1:A6=A13:A14,B1:B6,"")) but that returns #N/A. Why is the array function not correctly pulling the partial-sample order#?
    – Moses
    Apr 1, 2013 at 19:21
  • Disregard that. I was able to workaround this issue by making the initial cell an array function, then dragging the array function down to fill in the remaining table rows.
    – Moses
    Apr 1, 2013 at 19:27
  • @Moses Yep, just enter it in the first cell under Last_Match_Date (using Ctrl+Shift+Enter) then copy it down to the other cells. You were getting the error because you replaced A2 (or the lookup value) with another array not parallel to the arrays in the formula.
    – Ellesa
    Apr 2, 2013 at 3:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .