Match data from 2 work sheets and return the result to the first worksheet

[elanda]

Hello,

What I am looking for is vlookup type function that will look up the first 3 columns in data worksheet 1 and find it in the data lookup table and find the pay grade and if it is there return the Role Type to Data Sheet 1 Role Type.

Data Worksheet 1 (8k rows)
Division Name Cost Centre Name Pay Grd Role Type
ACUTE CARE Medicine Medical SC Return Role Type from Lookup Data
ACUTE CARE Medicine Medical DS1 Return Role Type from Lookup Data




Lookup Data (~400 rows)
Division Name Cost Centre Name Pay Grd1 Pay Grd2 Pay Grd3 Pay Grd4 Role Type
ACUTE CARE Medicine Medical SC2 SCM1 SDM1 SSC5 SC MED-REG
ACUTE CARE Medicine Medical DS2 DCM3 DDC1 DS1 DSC MED-INTERN

Any help appreciated I am sure it needs an index or an array. But I think it is beyond my comprehension.

Much thanks in advance

AE

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H
2	Division Name	Cost Centre Name	Pay Grd	Role Type
3	ACUTE CARE	Medicine Medical	SC2	MED-REG
4	ACUTE CARE	Medicine Medical	DS2	MED-INTERN
5
6
7
8
9	Lookup Data (~400 rows)
10	Division Name	Cost Centre	Name	Pay Grd1	Pay Grd2	Pay Grd3	Pay Grd4	Role Type
11	ACUTE CARE	Medicine Medical	SC2	SCM1	SDM1	SSC5	SC	MED-REG
12	ACUTE CARE	Medicine Medical	DS2	DCM3	DDC1	DS1	DSC	MED-INTERN
ADP

Cell Formulas
Range	Formula
D3	{=IFERROR(INDEX($H$11:$H$12,MATCH(A3&"|"&B3&"|"&C3,$A$11:$A$12&"|"&$B$11:$B$12&"|"&$C$11:$C$12,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[elanda]

How about

Excel 2013/2016

	A	B	C	D	E	F	G	H
Division Name	Cost Centre Name	Pay Grd	Role Type
ACUTE CARE	Medicine Medical	SC2	MED-REG
ACUTE CARE	Medicine Medical	DS2	MED-INTERN
Lookup Data (~400 rows)
Division Name	Cost Centre Name	Pay Grd1	Pay Grd2	Pay Grd3	Pay Grd4	Pay Grd5	Role Type
ACUTE CARE	Medicine Medical	SC3	SCM1	SDM1	SSC5	SC2	MED-REG
ACUTE CARE	Medicine Medical	DS3	DS2	DDC1	DS1	DSC	MED-INTERN

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

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

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

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

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

[TD="align: right"][/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: 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: 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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

</tbody>

ADP​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]D3[/TH]
[TD="align: left"]{=IFERROR(INDEX($H$11:$H$12,MATCH(A3&"|"&B3&"|"&C3,$A$11:$A$12&"|"&$B$11:$B$12&"|"&$C$11:$C$12,0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Much thanks Fluff.

This works great.

Cheers

AE

 

[Fluff]

You're welcome & thanks for the feedback