Vlookup OR Index/Match function with multiple criteria

[ld1414]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Employee ID[/TD]
[TD] Position #[/TD]
[TD]Position Status[/TD]
[/TR]
[TR]
[TD]Employee A[/TD]
[TD] 111111111[/TD]
[TD] 111111[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Employee A[/TD]
[TD]111111111[/TD]
[TD] 777777[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]Employee B[/TD]
[TD]222222222[/TD]
[TD]222222[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Employee C[/TD]
[TD]333333333[/TD]
[TD]333333[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Employee D[/TD]
[TD]444444444[/TD]
[TD]444444[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]Employee D[/TD]
[TD]444444444[/TD]
[TD]555555[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hello there,

I'm attempting to setup a Index match or Vlookup formula, but having issues when doing so with mutiple criteria. Hoping for help as I'm confused with other threads and email searches.

Currently my formula is setup to look at employee ID and pull job data relating to a position from the employee. The issue though is that some employees here have multiple positions where they may not necessary be active. I need a formula that allow me to pull employee ID- and then only look at the active (A) position data. The table below is how information looks. Employee A and D for example have the same name/ID, but may have 2 different positions. Thank you for any assistance

 

[Fluff]

How about

Book1
	A	B	C	D	E	F
1	Employee Name	Employee ID	Position #	Position Status		444444444
2	Employee A	111111111	111111	A		555555
3	Employee A	111111111	777777	W
4	Employee B	222222222	222222	A
5	Employee C	333333333	333333	A
6	Employee D	444444444	444444	W
7	Employee D	444444444	555555	A
Sheet2

Cell Formulas
Range	Formula
F2	=INDEX(C2:C7,MATCH(F1&"|A",INDEX(B2:B7&"|"&D2:D7,0),0))

 

[ld1414]

How about

	A	B	C	D	E	F
Employee Name	Employee ID	Position #	Position Status
Employee A	A
Employee A	W
Employee B	A
Employee C	A
Employee D	W
Employee D	A

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet2​

[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] "]F2[/TH]
[TD="align: left"]=INDEX(C2:C7,MATCH(F1&"|A",INDEX(B2:B7&"|"&D2:D7,0),0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Perfection! Thank you sir. I sadly still don't quite understand the formula, but it works nonetheless.

 

[Fluff]

It's concatenating the value in F1 with |A and concatenating the Values in colb with a | and the values in col d.
So you would end up looking for 444444444|A in a list of values like

Book1
	H
2	111111111|A
3	111111111|W
4	222222222|A
5	333333333|A
6	444444444|W
7	444444444|A
Sheet2