Define and Reference fixed data in a table
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 Dick 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 |
Define and Reference fixed data in a table
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" a écrit dans le message de news: ... 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 Dick 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 |
Define and Reference fixed data in a table
Thanks Ardus - works great, although I had to use MATCH instead of
EQUIV for some reason. |
Define and Reference fixed data in a table
The reason is I'm a bloody frenchman, and forgot to translate the formula!
Cheers, -- AP "RedHook" a écrit dans le message de news: ... Thanks Ardus - works great, although I had to use MATCH instead of EQUIV for some reason. |
Define and Reference fixed data in a table
With your present configuration, you might try the "intersection operator",
which is a <Space. =Bravo Dick OR =Dick 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" wrote in message oups.com... Thanks Ardus - works great, although I had to use MATCH instead of EQUIV for some reason. |
All times are GMT +1. The time now is 09:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com