Complex VLookup?

darren_ireland

New Member
Joined
Sep 11, 2019
Messages
3
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.

AccountsHRAdmin
Entity AJoe BloggsJane Bloggs
Entity B
Entity C
Vlookup 1Vlookup 2Result (=Entity & Admin)
EntityDepartmentJane Bloggs

<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>
</tbody>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi & welcome to MrExcel.
How about


Book1
ABCD
1AccountsHRAdmin
2Entity AJoe BloggsJane Bloggs
3Entity B
4Entity C
5
6
7
8Entity AAdminJane Bloggs
Dont
Cell Formulas
RangeFormula
C8=INDEX(B2:D4,MATCH(A8,A2:A4,0),MATCH(B8,B1:D1,0))
 
Upvote 0
With your dropdowns in G7 and H7, try =VLOOKUP(G7,A1:D4,MATCH(H7,1:1,))
 
Last edited:
Upvote 0
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

ABCD
1AccountsHRAdmin
2Entity AJoe BloggsJane Bloggs
3Entity B
4Entity C
5
6
7
8Entity AAdminJane Bloggs

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Dont

Worksheet Formulas
CellFormula
C8=INDEX(B2:D4,MATCH(A8,A2:A4,0),MATCH(B8,B1:D1,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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,))
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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