Limited scope VLookup

[Demirion]

Hi. I have a table in which I assign team members to specific tasks using the vlookup. I would like to each team member should have 20 requests assigned. The rest of requests should receive the status "unassigned". What's the easiest way to do this?

 

[Legacy 143009]

How about something like this?

=IF( COUNTIF(TableRange, VLOOKUP("For Employe Name"))>20;"unassigned";VLOOKUP("For Employe Name"))

 

[Demirion]

Maybe but I don't know how use it. My vlookup looks like:

Range("B2:B" & lastRow).Formula = "=VLOOKUP(A2,'" & wb1.Sheets("Sheet1").Name & "'!$A$1:$C & lastRow2 & ",2,False)"

 

[Legacy 143009]

Maybe like this?

Range("B2:B" & lastRow).Formula =IF( COUNTIF("B2:B" & lastRow, VLOOKUP(A2,'" & wb1.Sheets("Sheet1").Name & "'!$A$1:$C & lastRow2 & ",2,False))>20;"unassigned";VLOOKUP(A2,'" & wb1.Sheets("Sheet1").Name & "'!$A$1:$C & lastRow2 & ",2,False))

 

[Demirion]

I don't know if this formula is correct. I have a syntax error. Can you take a look?

.Range("B2:B" & lastRow).Formula = If(CountIf("B2:B" & lastRow, VLOOKUP(A2,'" & wb1.Sheets("Sheet1").Name & "'!$A$1:$C & lastRow2 & ",2,False))>20,"unassigned",VLOOKUP(A2,'" & wb1.Sheets("Sheet1").Name & "'!$A$1:$C & lastRow2 & ",2,False)

 

[Legacy 143009]

There were misplaced quotation marks. Try like that:

Range("B2:B" & lastRow).Formula = "=If(CountIf(B2:B" & lastRow & ", VLOOKUP(A2,'" & wb.Sheets("Sheet1").Name & "'!$A$1:$C" & lastRow2 & ",2,False))>20,""unassigned"",VLOOKUP(A2,'" & wb1.Sheets("Sheet1").Name & "'!$A$1:$C" & lastRow2 & ",2,False)"

 

[Demirion]

It works without syntax errors, but I got an error when I ran the macro "Application-defined or object-defined error". Maybe the data range is wrong? I have tried various options but cannot solve the problem.

 

[Legacy 143009]

Yes workbook name or worksheet name can be wrong. Why do you want to go with VBA?

It is quite achievable with a regular formula.

 

[Demirion]

But in my earlier vlookup everything works fine. Something is wrong with this code, and I can't figure out what, if any, is correct.

Because I need to do a macro.

Can you send me some links on how to do it? Maybe I'll try to figure it out myself using a different method. Previously, I searched the internet but found little.

 

[Legacy 143009]

My bad.
Fix the first one

wb.Sheets("Sheet1").Name

It should be wb1 I guess