I have an IF(OR and vlookup Proplem

[Tom90]

Hi Tom90 hear and I am looking for a bit of help / guidance, I have an excel file with I hope to use to issues jobs from, but they meet condition and for that I am using an IF(OR formula wit a vlookup at the end but it is causing me problems as with vlookup it will always pick the first name that it comes to that the IF(OR and picked for it.
I have a file with Name, and work area, and what day's they are at work, the next file has work area and job Number,
I am using the IF(A2="IN",VLOOKUP(A2,work area:Job Number,2,0) but as there are a few job area that are the same the lookup will always return the job number that I pick that matches the formula, How can I make stop doing this and move onto the next job the meets the formula, I have attached a file so that anyone can understand it a bit better but what I am look for is if the engineer is in at work I need to give him a job but not the same twice as the VLOOKUO is doing that,

Hope someone can help Thanks Tom90

 

[xlstime]

Hello Tom,

It would be great if you can share the desire output

Hi Tom90 hear and I am looking for a bit of help / guidance, I have an excel file with I hope to use to issues jobs from, but they meet condition and for that I am using an IF(OR formula wit a vlookup at the end but it is causing me problems as with vlookup it will always pick the first name that it comes to that the IF(OR and picked for it.
I have a file with Name, and work area, and what day's they are at work, the next file has work area and job Number,
I am using the IF(A2="IN",VLOOKUP(A2,work area:Job Number,2,0) but as there are a few job area that are the same the lookup will always return the job number that I pick that matches the formula, How can I make stop doing this and move onto the next job the meets the formula, I have attached a file so that anyone can understand it a bit better but what I am look for is if the engineer is in at work I need to give him a job but not the same twice as the VLOOKUO is doing that,

Hope someone can help Thanks Tom90

 

[xlstime]

Hi Tom,

See if this help.

Hello Tom,

It would be great if you can share the desire output

 

[GeraldDLT]

Hi Tom90,

If my understanding is right, could you just make values in Work Area unique?
instead of F,F make it F1,F2. If you find this wrong, please disregard.

 

[bosco_yip]

Maybe.........

In D2, array formula (confirm entered with SHIFT+CTRL+ENTER 3 keys together) copy down :

=IF(B2=J$1,INDEX(H$2:H$10,SMALL(IF(G$2:G$10=A2,ROW(G$2:G$10)-ROW(G$2)+1),COUNTIF(A$2:A2,A2))),"NO JOB")

Regards
Bosco

 

[Tom90]

Hi xlstime & bosco_yip, sorry for the late response but I have just sign on, thanks as both you answer work well and just what I need sorry my explanation was not clear but you work it out anyway. I was looking for a formula with the vlookup to move to the next job and not as it was in my example were it just repeated the same job, Thanks again Tom90