[SOLVED] Define and Reference fixed data in a table

[RedHook]

Hi All

I'm new to Excel and would like to know if it's possible to do the
following:

Define a table(2 dimensional array) of fixed data like this:

Tom **** Harry

Alpha 10 7 5

Bravo 6 27 4

Charlie 17 22 8


I'd then like to be able to reference each element in the array using
the row and column identifiers as indexes, so (Alpha, Harry) would
return 5, (Charlie,Harry) would return 8 and so on.

Tia
RH

[Ardus Petus]

Say you have Alpha in F1, Harry in G1 and your data (including headers) in
A1:D4
Enter formula:
=INDEX(B2:D4,EQUIV(F1,A2:A4,0),EQUIV(G1,B1:D1,0))

HTH
--
AP

"RedHook" <[email protected]> a écrit dans le message de news:
[email protected]...
> Hi All
>
> I'm new to Excel and would like to know if it's possible to do the
> following:
>
> Define a table(2 dimensional array) of fixed data like this:
>
> Tom **** Harry
>
> Alpha 10 7 5
>
> Bravo 6 27 4
>
> Charlie 17 22 8
>
>
> I'd then like to be able to reference each element in the array using
> the row and column identifiers as indexes, so (Alpha, Harry) would
> return 5, (Charlie,Harry) would return 8 and so on.
>
> Tia
> RH
>

[RedHook]

Thanks Ardus - works great, although I had to use MATCH instead of
EQUIV for some reason.

[Ardus Petus]

The reason is I'm a bloody frenchman, and forgot to translate the formula!

Cheers,
--
AP

"RedHook" <[email protected]> a écrit dans le message de news:
[email protected]...
> Thanks Ardus - works great, although I had to use MATCH instead of
> EQUIV for some reason.
>

[RagDyeR]

With your present configuration, you might try the "intersection operator",
which is a <Space>.

=Bravo ****
OR
=**** Bravo
Will return 27

AS well as
=Alpha Harry
Will return 5

And
=Tom Charlie
Will return 17


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"RedHook" <[email protected]> wrote in message
news:[email protected]...
Thanks Ardus - works great, although I had to use MATCH instead of
EQUIV for some reason.