Complex VLookup?

[darren_ireland]

Hi there,

Sorry to bother you but I was wondering if any Excel experts could give some guidance on a VLookup question?

I am trying to look-up two combined records. I have a sheet with Entity 1, Entity 2, Entity 3 as columns, then as rows (headers) I have departments (Accounts, HR, Admin). I want to be be able to have a drop down in a preview sheet with the entity and department and then for Excel to lookup and populate the person in each dept?

This is what I am trying to achieve (below) - in my overview sheet if I have "Entity A" with "Admin" I would like to see that "Jane Bloggs" is the contact - similarly if I had "Entity A" with "Accounts" it would show "Joe Bloggs"

I have the department and entity as drop down to assist with the lookup command. I tried unsuccessfully tried to use the concatenate formula but it just got very messy and didn't work.

Thanks a million for your time reading this!!

Darren.

[TABLE="width: 348"]
<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 87"][/TD]
[TD="class: xl63, width: 87, bgcolor: yellow"]Accounts[/TD]
[TD="class: xl63, width: 87, bgcolor: yellow"]HR[/TD]
[TD="class: xl63, width: 87, bgcolor: yellow"]Admin[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: yellow"]Entity A[/TD]
[TD]Joe Bloggs[/TD]
[TD][/TD]
[TD]Jane Bloggs[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: yellow"]Entity B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: yellow"]Entity C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vlookup 1[/TD]
[TD]Vlookup 2[/TD]
[TD="colspan: 2"]Result (=Entity & Admin)[/TD]
[/TR]
[TR]
[TD="class: xl64"]Entity[/TD]
[TD="class: xl64"]Department[/TD]
[TD="class: xl63, bgcolor: yellow"]Jane Bloggs[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D
1		Accounts	HR	Admin
2	Entity A	Joe Bloggs		Jane Bloggs
3	Entity B
4	Entity C
5
6
7
8	Entity A	Admin	Jane Bloggs
Dont

Cell Formulas
Range	Formula
C8	=INDEX(B2:D4,MATCH(A8,A2:A4,0),MATCH(B8,B1:D1,0))

 

[BenMiller]

With your dropdowns in G7 and H7, try =VLOOKUP(G7,A1:D4,MATCH(H7,1:1,))

 

[darren_ireland]

Hi Fluff!

Thanks a million for your quick solution! I tried this and it works perfectly. I don't seem to be able to press "Thanks" but a very big thank-you!

Have a nice evening!

Darren.

Hi & welcome to MrExcel.
How about

	A	B	C	D
Joe Bloggs	Jane Bloggs
Entity A	Admin	Jane Bloggs

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Accounts[/TD]
[TD="bgcolor: #FFFF00"]HR[/TD]
[TD="bgcolor: #FFFF00"]Admin[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]Entity A[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFF00"]Entity B[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFF00"]Entity C[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

</tbody>

Dont​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=INDEX(B2:D4,MATCH(A8,A2:A4,0),MATCH(B8,B1:D1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[darren_ireland]

Thanks BenMiller!

I appreciate you getting back with your solution so soon! I have played around with your formula and it works perfectly too.

I feel like it was such a basic question but obviously not for the less well experienced!

Great to have expert advice!

Take it easy, Darren.

With your dropdowns in G7 and H7, try =VLOOKUP(G7,A1:D4,MATCH(H7,1:1,))

 

[Fluff]

Glad to help & thanks for the feedback