Select columns, compare dates and display column title with oldest date

[rluesc]

I have a large number of columns with a title in row 2 and a date in row 3. For example, the column titled “squat air” is dated 12/28/2015.

I want to select individual columns, for example squat air and squat oh, find the column with the oldest date in this selection, then display the title of that column.

[Tony Valko]

All the column headers are unique.

It's not clear what result you expect.

Can you update the file to include the result you expect?

[Pete_UK]

You could use column H (say) to list the column Headings that you want to consider, then you can use this array* formula in J1 to get the oldest date:

=MIN(IF(ISNUMBER(MATCH(A2:F2,H:H,0)),A3:F3))

Format the cell as a date.

*Note that an array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual |Enter|.

Hope this helps.

Pete

[rluesc]

All the column headers are unique.

It's not clear what result you expect.

Can you update the file to include the result you expect?

If I select column A, C and F I want the formula to yield "squat air" because that's the column with the oldest date.

[Tony Valko]

If I select column A, C and F I want the formula to yield "squat air" because that's the column with the oldest date.

I think Pete's suggestion will do what you want.

[Tony Valko]

I think Pete's suggestion will do what you want.

Actually, Pete's formula will return the date.

If you want the corresponding column header:

H2:H4 = squat air, squat front, farmer squat

Then, this array formula**:

=INDEX(A2:F2,MATCH(MIN(IF(ISNUMBER(MATCH(A2:F2,H2:H4,0)),A3:F3)),IF(ISNUMBER(MATCH(A2:F2,H2:H4,0)),A3:F3),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[rluesc]

You could use column H (say) to list the column Headings that you want to consider, then you can use this array* formula in J1 to get the oldest date:

=MIN(IF(ISNUMBER(MATCH(A2:F2,H:H,0)),A3:F3))

Format the cell as a date.

*Note that an array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual |Enter|.

Hope this helps.

Pete

This yields the column's date. I need the column's title.

Also, I want to plug specific columns into the formula. Say out of 100 columns total, today I want to search in columns A, B, E and F and tomorrow I want to look in columns U, X, Y and AM.

[Pete_UK]

Here's the file, to show it working - just add or delete your column headings from column H.

Hope this helps.

Pete

[rluesc]

Thanks for the clarification, this does the trick.

Having extra columns with the relevant headings is an inconvenience. Any way around that?

[Tony Valko]

None that I can think of.

You have to have some method of telling the formula where to look for the info. Using the column headers is the best method.

[rluesc]

I'm currently working with this:

=index(a2:f2,match(min(a3:f3),a3:f3,0))

Can you rewrite the formula so it does not contain all columns A through F but select columns, for example A and C?

[Tony Valko]

The only way I know how to do it is described in post #6.

[Tony Valko]

The only way I know how to do it is described in post #6.

Well, this is technically different but it uses the same logic.

On row 1 you could mark the columns of interest with an "X" (or whatever) then you'd tweak the formula to look for the columns marked with "X" (or whatever).

Either way, you still have to identify in which columns to look for the data. Why are you so averse to listing the column headers? That is the most logical option.