Vlookup Multible Entires (Conditions

[ochiha]

Hello there,
I have two sheets, the first one includes employee requests where an employee can have more than one listed request, some of them are market "DONE", Some marked "Rejected".
what am I trying to do is link this sheet with another sheet that include employee vacation/ Cancellation, where there will be a cell that shows if there are any under process applications for the employees before they leave on vacation.
The limitations and exlusions in this formula are:
1. Multible requests
2. "Done" and "Rejected" requests (Must be excluded)
3. I need the result to appear in the employee vacation sheet

THis is as far as I went: =IF(OR(VLOOKUP(D3,'Employee Request'!C$3:G$551,5,0)="Done",VLOOKUP(D3,'Employee Request'!C$3:G$551,5,0)="Rejected"),"",MATCH(D3,'Employee Request'!C$3:G$374,0))
Employee Vacation Sheet

Employee Request Sheet

 

[CharlesChuckieCharles]

How about using a countifs instead (since effectively you want to know how many)



=COUNTIFS('Employee Request'!C$3:G$551,"=" & G16)-(COUNTIFS('Employee Request'!C$3:G$551,"=" & G16,'Employee Request'!C$3:G$551,"=Done")+COUNTIFS('Employee Request'!C$3:G$551,"=" & G16,'Employee Request'!C$3:G$551,"=Rejected"))

 

[ochiha]

How about using a countifs instead (since effectively you want to know how many)



=COUNTIFS('Employee Request'!C$3:G$551,"=" & G16)-(COUNTIFS('Employee Request'!C$3:G$551,"=" & G16,'Employee Request'!C$3:G$551,"=Done")+COUNTIFS('Employee Request'!C$3:G$551,"=" & G16,'Employee Request'!C$3:G$551,"=Rejected"))

isnt there a possibility to know the request type?

 

[CharlesChuckieCharles]

If you have multiple request whcih aren't Done or rejected how would you handle that

 

[ochiha]

I Have no Clue

 

[CharlesChuckieCharles]

What to you want to put in your spreadsheet if you have more than one outstanding valid request?

 

[ochiha]

What to you want to put in your spreadsheet if you have more than one outstanding valid request?

I would like to put the first request

 

[CharlesChuckieCharles]

Is that just the first in the list regardless? or is there a logic to the order of the source data? As in, descending by decision date or submission date<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
if someone has 7 outstanding Vacation requests is that relevant compared to someone who has one)
<o> </o>
you probably need to create a pivot table of the source but getting the first of anything in excel depends on order and magnitude
<o> </o>
VLOOKUP probably won’t get you what you need BUT
<o> </o>
From the example getting the first entry by employee number would need the source workbook sorted by employee number then sorted by decision date (descending?) or submission date and hoping the first in list isn’t done hiding the next in list which might be Under consideration…..<o></o>

You need to know what you’re trying to achieve

at least with the "countifs" you know when you have something relevant
(obviously you should change the address items to suit your data)

=COUNTIFS('Employee Request'!$C$3:$C$33,"=" & $C3)-(COUNTIFS('Employee Request'!$C$3:$C$33,"=" & $C3,'Employee Request'!$D$3:$D$33,"=Done")+COUNTIFS('Employee Request'!$C$3:$C$33,"=" & $C3,'Employee Request'!$D$3:$D$33,"=Rejected"))

 

[ochiha]

Is that just the first in the list regardless? or is there a logic to the order of the source data? As in, descending by decision date or submission date<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
if someone has 7 outstanding Vacation requests is that relevant compared to someone who has one)
<o> </o>
you probably need to create a pivot table of the source but getting the first of anything in excel depends on order and magnitude
<o> </o>
VLOOKUP probably won’t get you what you need BUT
<o> </o>
From the example getting the first entry by employee number would need the source workbook sorted by employee number then sorted by decision date (descending?) or submission date and hoping the first in list isn’t done hiding the next in list which might be Under consideration…..<o></o>

You need to know what you’re trying to achieve

at least with the "countifs" you know when you have something relevant
(obviously you should change the address items to suit your data)

=COUNTIFS('Employee Request'!$C$3:$C$33,"=" & $C3)-(COUNTIFS('Employee Request'!$C$3:$C$33,"=" & $C3,'Employee Request'!$D$3:$D$33,"=Done")+COUNTIFS('Employee Request'!$C$3:$C$33,"=" & $C3,'Employee Request'!$D$3:$D$33,"=Rejected"))

I think you are right...
I think the countif will do just fine...
thank you for your time and assitance.
appreciate it