I have an array - say A2:F20 which contains different names of 6 types of
referral source, where the 6 names in row 2 are the titles of the types and
the names are listed in the appropriate columns below each title.

Is there a formula I can use in Excel so that if I type a the name of a
referral source in say cell A22, cell B22 will return the appropriate type
title.

I have looked up help on using lookup tables but none of the functions seem
to provide what I want, although I can't believe it can't be done!

Thanks,

V

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.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Victor Delta" wrote in message
...
I have an array - say A2:F20 which contains different names of 6 types of
referral source, where the 6 names in row 2 are the titles of the types
and
the names are listed in the appropriate columns below each title.

Is there a formula I can use in Excel so that if I type a the name of a
referral source in say cell A22, cell B22 will return the appropriate type
title.

I have looked up help on using lookup tables but none of the functions
seem
to provide what I want, although I can't believe it can't be done!

Thanks,

V

"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?

V

"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

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

"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