What formula can I set up to do a cross lookup in an Excel table?

[Guest]

I need to copy data from a table into a template where both column and row
headers are the same only mixed up. If I do a vlookup, it will match one
column of data but I will have to apply the formula separately to each row
and vice versa. Here's an example of how it looks:
Table 1:
A C B D
1
2
3
4
to be copied into a template with the follwing structure:
B D C A
3
4
1
2

Is there one formula to match both the row and colum data at the same time?
Thanks!

 

[Guest]

You can do this using a combination of the INDEX and MATCH worksheet functions.

=INDEX($B$2:$E$5,MATCH($A10,$A$2:$A$5,0),MATCH(B$9,$B$1:$E$1,0))

The above formula will work assuming that the values you want to appear in
the template are in cells B2:E5, The row lookup value is in column A, The Row
lookup range is in cells A2:A5, The column lookup vlaue is in row 9, The
column lookup range is in row 1.

In my example above everything was on the same sheet. Table 1 was in cells
A1:E5 and the template was in cells A9:E13. Both areas included row and
column headings.

Hope this helps.
Bill Horton