Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula can I set up to do a cross lookup in an Excel table?
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 structu 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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula can I set up to do a cross lookup in an Excel table?
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 "grigorovag" wrote: 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 structu 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Lookup formula returning wrong results? | Excel Worksheet Functions | |||
Pasting Word table cell with paragraph markers into single Excel c | Excel Discussion (Misc queries) | |||
formula to lookup table in another Worksheet please | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
HELP! Excel Lookup formula | Excel Worksheet Functions |