Hi everyone,
I was wondering if you could please help me. I have two tables on that I want to present information (table 1) and the other that lists the data (table 2). Table one shows the area, sub area and item and I would like to create a formula in the item code column that returns the appropriate value from table 2 based on the criteria of item and sub area.
My problem is that I have basic knowledge of index and match functions and cannot seem to create a way to look up a column that is unknown.
Thank you for your help.
Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Area Code[/TD]
[TD]Sub Area[/TD]
[TD]Sub Area Code[/TD]
[TD]Item[/TD]
[TD]Item Code[/TD]
[/TR]
[TR]
[TD]Finance[/TD]
[TD]006-02-08[/TD]
[TD]Accounting[/TD]
[TD]006-02-08-001[/TD]
[TD]Training[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HR[/TD]
[TD]006-02-06[/TD]
[TD]Recruiting[/TD]
[TD]006-02-06-003[/TD]
[TD]Vacation[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Accounting[/TD]
[TD]006-02-08-001[/TD]
[TD]Recruiting[/TD]
[TD]006-02-06-003[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]006-02-08-001-004[/TD]
[TD]Personnel[/TD]
[TD]006-02-06-003-001[/TD]
[/TR]
[TR]
[TD]Computers[/TD]
[TD]006-02-08-001-005[/TD]
[TD]Rellocation[/TD]
[TD]006-02-06-003-002[/TD]
[/TR]
[TR]
[TD]Operating Costs[/TD]
[TD]006-02-08-001-006[/TD]
[TD]Vacation[/TD]
[TD]006-02-06-003-003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I was wondering if you could please help me. I have two tables on that I want to present information (table 1) and the other that lists the data (table 2). Table one shows the area, sub area and item and I would like to create a formula in the item code column that returns the appropriate value from table 2 based on the criteria of item and sub area.
My problem is that I have basic knowledge of index and match functions and cannot seem to create a way to look up a column that is unknown.
- Could you please help me to create a forumla that is able to look across to find the appropriate column and then down to find the approproate item and then return the item code?
- Is there a way to do the above if I have each sub area on a separate tab?
Thank you for your help.
Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Area Code[/TD]
[TD]Sub Area[/TD]
[TD]Sub Area Code[/TD]
[TD]Item[/TD]
[TD]Item Code[/TD]
[/TR]
[TR]
[TD]Finance[/TD]
[TD]006-02-08[/TD]
[TD]Accounting[/TD]
[TD]006-02-08-001[/TD]
[TD]Training[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HR[/TD]
[TD]006-02-06[/TD]
[TD]Recruiting[/TD]
[TD]006-02-06-003[/TD]
[TD]Vacation[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Accounting[/TD]
[TD]006-02-08-001[/TD]
[TD]Recruiting[/TD]
[TD]006-02-06-003[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]006-02-08-001-004[/TD]
[TD]Personnel[/TD]
[TD]006-02-06-003-001[/TD]
[/TR]
[TR]
[TD]Computers[/TD]
[TD]006-02-08-001-005[/TD]
[TD]Rellocation[/TD]
[TD]006-02-06-003-002[/TD]
[/TR]
[TR]
[TD]Operating Costs[/TD]
[TD]006-02-08-001-006[/TD]
[TD]Vacation[/TD]
[TD]006-02-06-003-003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]