Sorry, I didn't test for non-existent matches.

You're right, with no match found, the original formula always returns the
first column, although it *does work* when matches are present ... with no
sorting necessary.
If duplicates matches exist, the column of the last match is returned.

Try this formula, also an *array* formula, if there is the possibility of
non-existent matches ... where I included a text statement as part of the
error trap:

=IF(SUM(--ISNUMBER(SEARCH(A22,A3:F20))),INDEX(A2:F2,
MAX((A3:F20=A22)*(COLUMN(A:F)))),"No Match")

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

As in the original formula, if duplicate matches exist, the column of the
*last* match is returned.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Victor Delta" wrote in message
...
"Victor Delta" wrote in message
...
"Ragdyer" wrote in message
...
Try this *array* formula:

=INDEX(A2:F2,MAX(IF(A3:F20=A22,COLUMN(A:F))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

Many thanks. Am I right in thinking that the columns of names must be in
alphabetical order too?

Actually, your formula does not seem to give consistent results - with or
without the data in alphabetical order. Also, if there is no match I would
prefer an error code or something to indicate this.

Am I doing something wrong or is it
your formula????

Thanks,

V