How to Select Cell by Multiple Columns

[JaedenRuiner]

Okay,

imagine two columns: A9:A10, and H9:H10. All 5 cells have Date values in them, but they could be in any order. In M8:M12 range, I want a list of the Cells: (A9,H10,A10,H9,H11) in order of the date values, but without using VBA.
Can I do this?
Currently I have L8:L12 containing the formula: SMALL(($A$9:$A$10,$H$9:$H$11), ROW(1:1))
which naturally the ROW(1:1) becomes 2:2, 3:3 etc down the line so i get the 1,2,3,4,5th smallest number in the original array. However, MATCH and LOOKUP do not seem to like the same "Array" notation as SMALL will allow. I've been trying to figure out how to get each cell value in sequence but so far no luck.
Any ideas?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

[Paul]

Hi Jaeden.

Well you have me lost, so hopefully we can get clarification..

You have five cells: A9, A10, H9, H10, H11
Each of those cells have a date in them.
In M8:M12 (five cells) you want those dates in order.

Isn't that what L8:L12 is doing? How does INDEX and MATCH come into play? What values do you expect in M8:M12?

Perhaps a sample workbook would help, showing before/after and why.

[beaunydal]

If you can guarantee that the five dates are always unique then maybe something like Sheet1 in the attached workbook.
Otherwise, maybe something like Sheet2.

Beau Nydal

[Paul]

After looking at Beau's reply, if you're only after the address of where the dates in L8:L12 were found, you can use this in M8 and filled down to M12:

=IFERROR("A"&MATCH(L8,$A$9:$A$10,0)+8,"H"&MATCH(L8,$H$9:$H$11,0)+8)

No need for helper columns.

In Excel 2003 and earlier it would need to be:

=IF(ISERROR("A"&MATCH(L8,$A$9:$A$10,0)+8),"H"&MATCH(L8,$H$9:$H$11,0)+8,"A"&MATCH(L8,$A$9:$A$10,0)+8)

[JaedenRuiner]

Ah,
Yes, maybe I should have been a little clearer, because if this was something "simple" i would have already solved it. 5 dates, in 5 different cells, non-contiguous, non-unique.

I'm looking for a way to get the sequential list of dates, first by the date value, then by cell order (ie: top-down,left-right, A9 before A10, before H11, etc).

So my current problem is when H9 - H11 are all the same date, I cannot get the Middle value. These are being used to 'lookup' values in parallel columns so I need to get the location.

this time i've attached a small xls file that has two grids in it and a description of what I am trying to accomplish. I believe once you look at it, the two different states (values and orders) will be clear enough to explain what I'm attempting to achieve.
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner