# Thread: Vlookup OR Index/Match function with multiple criteria Thanks:  2 Post #5341608 (1)Post #5341649 (1) Likes:  2 Post #5341608 (1)Post #5341649 (1)

1. ## Vlookup OR Index/Match function with multiple criteria

 Employee Name Employee ID Position # Position Status Employee A 111111111 111111 A Employee A 111111111 777777 W Employee B 222222222 222222 A Employee C 333333333 333333 A Employee D 444444444 444444 W Employee D 444444444 555555 A

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

2. ## Re: Vlookup OR Index/Match function with multiple criteria

ABCDEF
1Employee NameEmployee IDPosition #Position Status444444444
2Employee A111111111111111A555555
3Employee A111111111777777W
4Employee B222222222222222A
5Employee C333333333333333A
6Employee D444444444444444W
7Employee D444444444555555A

Sheet2

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

3. ## Re: Vlookup OR Index/Match function with multiple criteria

Originally Posted by Fluff

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

Worksheet Formulas
Cell Formula
F2 =INDEX(C2:C7,MATCH(F1&"|A",INDEX(B2:B7&"|"&D2:D7,0),0))
Perfection! Thank you sir. I sadly still don't quite understand the formula, but it works nonetheless.

4. ## Re: Vlookup OR Index/Match function with multiple criteria

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

H
2111111111|A
3111111111|W
4222222222|A
5333333333|A
6444444444|W
7444444444|A

Sheet2