Vlookup OR Index/Match function with multiple criteria

ld1414

New Member
Joined
Mar 5, 2018
Messages
17
Employee NameEmployee ID Position #Position Status
Employee A 111111111 111111A
Employee A111111111 777777W
Employee B222222222222222A
Employee C333333333333333A
Employee D444444444444444W
Employee D444444444555555A

<tbody>
</tbody>

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
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
How about


Book1
ABCDEF
1Employee NameEmployee IDPosition #Position Status444444444
2Employee A111111111111111A555555
3Employee A111111111777777W
4Employee B222222222222222A
5Employee C333333333333333A
6Employee D444444444444444W
7Employee D444444444555555A
Sheet2
Cell Formulas
RangeFormula
F2=INDEX(C2:C7,MATCH(F1&"|A",INDEX(B2:B7&"|"&D2:D7,0),0))
 
Upvote 0
How about

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

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

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

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

<tbody>
</tbody>

Perfection! Thank you sir. I sadly still don't quite understand the formula, but it works nonetheless.
 
Upvote 0
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
2111111111|A
3111111111|W
4222222222|A
5333333333|A
6444444444|W
7444444444|A
Sheet2
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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