Try this:

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

Still an *array* formula.
--
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.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Victor Delta" wrote in message
...
"Ragdyer" wrote in message
...
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")

Many thanks - that's a great improvement.

The only funny thing now is that if you enter any letter or letters that
correspond with part of one of the names, you get the first column
selected
instead of 'No Match'. Don't suppose it is possible to fix as well this
please?

Thanks,

V