complex look up - unknown column reference

ctrlexcel

New Member
Joined
Sep 10, 2014
Messages
2
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.

  1. 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?
  2. 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]
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Assumed your Table1 in ranges A1:F3 (include header), and your Table2 in ranges H2:K5

Put this Array Formula inF2 in Table1:
=INDEX($H$3:$K$5,SMALL(IF((INDEX($H$3:$K$5,0,MATCH(C2,$H$2:$K$2,0))=$E2),MATCH(ROW($H$3:$H$5),ROW($H$3:$H$5)),""),1),MATCH($C2,$H$2:$K$2,0)+1)

As you know array formula means when ENTER you need to press CTRL-SHIFT-ENTER and copied down.

Im attaching the sample formula:
https://simpan.ugm.ac.id/public.php?service=files&t=91c92f96f46bdaca26e589de95e4635a
 
Upvote 0
Thank you so much for your help. This worked very well.

Do you know how I could achieve this same thing if the data from table 2 was split across multiple tabs? For example accounting and the corresponding codes on one tab and recruiting on another?
 
Upvote 0

Forum statistics

Threads
1,223,284
Messages
6,171,180
Members
452,388
Latest member
Lorenzo_Barry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top