Lookup & max, or match & offset within an array

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I'm trying to construct a formula that does the following:
Finds the maximum value (a date, if that matters) within row 2 of
sheet 1 that corresponds to a cell in row 1 (in the same column as the
max value), which in turn matches a value in column A of sheet 2. See
e.g. below: I'd like a formula which searches for "#" and finds
"March."

I am having difficulty combining the hlookup with a max function, and
similar difficulty combining the match and offset functions as an
alternative.

Sheet 1
A B C D
1 * # # @
2 Jan Feb March April

Sheet 2
A B
1 # FORMULA?
2 *
3 @

Any suggestsions?

Thanks,
Michael
 
One way:

Using the example you gave, enter this formula is A3 and copy across to d3

=A1&COUNTIF($A$1:A1,A1)

This will result in:

A B C D
1 * # # @
2 Jan Feb March April
3 *1 #1 #2 @1

Then on Sheet2 use this formula:

=INDEX(Sheet1!A2:D2,0,MATCH(A1&COUNTIF(Sheet1!A1:D1,A1),Sheet1!A3:D3,0))

HTH

-Dave
 
Thanks. The Hlookup finds the value, but does not direct the function
to look for the corresponding cell once the value is found. I need to
combine the Hlookup with another function. Or perhaps I've
misinterpreted your formula?
-ML
 
Back
Top