VLOOKUP formula for multiple fields/ranges?

[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.


Many thanks in advance!

[mikeTRON]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[manny88]

Hi, I don't think the question needs an attachment to explain or illustrate it further. It just requires a successful formula that would account for the 3 outcomes. The actual worksheet I am working on is only accessible at work and I cannot use that information for DPA reasons.


Just for this however, I have made a quick template of the file for you. The only difference on this are the columns, which I have highlighted in red. I am looking at Columns K and Column V. The lookup tables are in the User Table tab and the Group Table tab. You will be able to see a formula in Column V of my (failed) attempt.

Many Thanks

[manny88]

Maybe this? Looks to be okay, I think. I'll know for certain tomorrow morning IFERROR(VLOOKUP(K2,USER_Table!C:E,2,FALSE),IFERROR(VLOOKUP(K2,'Group Table'!A:A,1,FALSE),"Leavers"))

[manny88]

Resolved myself. FYI so thread can be closed. Answer:


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