VLOOKUP Formula for multiple ranges/fields?

[manny88]

There are 3 tabs - Data, User Table (Contains employee names and managers/department they belong to), and Summary. There are columns from A to AA in the Data tab. Between this range, Column I contains plain text, examples;

I2: "John Smith" (Employee found in User Table)
I3: "Customer" (Individual customer, no lookup available)
I4: "Alan Mann" (Former employee who is no longer in User Table)

I need to create a (v)Lookup in Column S2 down of the "Group" the fields in Column I belong to. Example;

I2: "John Smith" ------ S2: "Customer Service"
I3: "Customer" ------- S2: "Customer"
I4: "Alan Mann" ------- S2: "Leavers"

Where I am going wrong is i'm not able to figure out how to nest the vlookup and Iferrors? in the formula to account for all 3 outcomes.

My failed attempt is: =IFERROR(VLOOKUP(K6,USER_Table!C:E,2,FALSE),"")

Many thanks in advance!

 

[manny88]

My Failed attempt should have pasted above as: =IFERROR(VLOOKUP(I2,USER_Table!C:E,2,FALSE),"")

 

[Marcílio_Lobão]

manny88, Good evening.

Try to use:

S2 -->
=IF(I2="Customer", "Customer", IFERROR( VLOOKUP(I2,USER_Table!C:E,2,FALSE), "Leavers")

Is that what you want?
I hope it helps.

 

[manny88]

Hi Marcilio_Lobao,

Thank a lot for your reply, sorry it took so long to reply.I didn't see the response till this morning at work. The formula you provided was almost right. It helped me in the right direction to find the answer, so I am grateful for that.

Answer was:

=IF(LEFT(I2,8)="Customer","Created by Customer",IFERROR(VLOOKUP(I2,User_Table1[[Full_Name]:[Group]],2,FALSE),"Leavers))

Thread can now be closed.

 

[Marcílio_Lobão]

Thanks for the feedback.

Have a nice day!