Daily Challenge! Need help with multiple vlookup criteria

Hey Pro's!

I have a unique challenge that is giving me a hard time. I am trying to write an all in one formula with several lookups based on certain criteria. I have 25 line items that I am trying to assign a user to each, based on certain criteria. In the attached spreadsheet, there are 4 tabs. Tab 1 is the data tab. Tab 2 and 3 are the lookup reference tabs. Tab 4 is a list of forms that I only want the formula to apply to.

Here are the specifics of the formula I am trying to have it contain in one cell:

- Formula should only apply to items where the "Form" equals one of the forms listed in tab "List 3"
- Formula should only apply to items where the "Location" equals "GOS"
- If the Form = SMILE_ERR, the formula should only apply to Err Code B36 and B07 (column E).
- If the Location = "GOS" and Initials = "" or Initials = "STR", formula should look to "List 2" tab to pull back the ID based on sales office match.
- If the Location = "GOS" and Initials <> "" and Initials <> "STR", formula should look to "List 1" tab to pull back the ID based on initials match.
- If the Form = SMILE_ERR, the formula should only apply to Err Code B36 and B07 (column E).

Any help or guidance you can provide would be greatly appreciated!

There is no manually added data in the assign column - we will need some so that we can see what you are expecting. Please add some (about 10) to the file and attach again. Thanks.

Something like below?
=IFERROR(IF(SUMPRODUCT(ISNUMBER(MATCH(A2,'List 3'!$A$2:$A$10,0))*(F2="GOS")*(A2<>"SMILE_ERR")+(A2="SMILE_ERR")*(C2="B07")+(A2="SMILE_ERR")*(C2="B06"))=1,IF(OR(D2="",D2="STR"),VLOOKUP(Data!E2,'List 2'!$A$2:$B$16,2),VLOOKUP(Data!D2,'List 1'!$A$2:$B$7,2)),"NA"),"NA")

Though I suspect there's better method.

See attached.

Take a look at column g:

=IF(AND(ISNUMBER(MATCH(A2,'List 3'!$A$2:$A$10,0)),IFERROR(F2="GOS",FALSE)),IF(OR(D2="",D2="STR"),VLOOKUP(E2,'List 2'!$A$2:$B$16,2,FALSE),VLOOKUP(D2,'List 1'!$A$2:$B$7,2,FALSE)),"")

HOWEVER, this does not take account of "- If the Form = SMILE_ERR, the formula should only apply to Err Code B06 and B07 (column E)" as I have no idea what you mean by this. so, explain that again and also see if what I already have is heading in the correct direction...

Your updated file seems to be in conflict with your condition(s).

G4: PR_CORRESP isn't in your List3
G5: BROKER_CHG isn't in your List3
G18: ERR_CODE is B36 (not B06 or B07) - See EDIT:
G22: Initials = "STR" should it not lookup in List2?
G26: ERR_CODE is B07 And Initials <> "" Or "STR", so should it not lookup in List1?

To return blank instead of "NA" just replace "NA" with "" in the formula found in Post #3

EDIT: Ah I see you had B36 & B07 intially, but had B06 & B07 in second instance in your condition. I had missed that.
Replace "B06" in the formula with "B36", if that's what you need.

Correct. The error codes should be B36 and B07 for Smile_Error forms. However, the list of forms on the data tab shouldn't drive the formula except for the smile_err. In the system, some forms may or may not appear in the data so I can't have that be a driving factor. Attached is the expected outcome.

Based on your comment at Post 9, please check your expected answer for rows 4-6...

I am currently looking at this one:

=IF(OR(AND(ISNUMBER(MATCH(A2,'List 3'!$A$2:$A$10,0)),IFERROR(F2="GOS",FALSE)),AND(A2="SMILE_ERR",OR(C2="B07",C2="B36"))),IF(OR(D2="",D2="STR"),VLOOKUP(E2,'List 2'!$A$2:$B$16,2,FALSE),VLOOKUP(D2,'List 1'!$A$2:$B$7,2,FALSE)),"")

Ok. Line 26 fixed. But for line 22, when Initials = "STR" shouldn't formula lookup in List2?

- If the Location = "GOS" and Initials = "" or Initials = "STR", formula should look to "List 2" tab to pull back the ID based on sales office match.

Current formula:
=IFERROR(IF(SUMPRODUCT(ISNUMBER(MATCH(A22,'List 3'!$A$2:$A$10,0))*(F22="GOS")*(A22<>"SMILE_ERR")+(A22="SMILE_ERR")*(C22="B07")*(F22="GOS")+(A22="SMILE_ERR")*(C22="B36"))*(F22="GOS")=1,IF(OR(D22="",D22="STR"),VLOOKUP(Data!E22,'List 2'!$A$2:$B$16,2),VLOOKUP(Data!D22,'List 1'!$A$2:$B$7,2)),""),"")

Edit: Took redundant check outside of SUMPRODUT to make it slightly shorter.
=IFERROR(IF(SUMPRODUCT(ISNUMBER(MATCH(A2,'List 3'!$A$2:$A$10,0))*(A2<>"SMILE_ERR")+(A2="SMILE_ERR")*(C2="B07")+(A2="SMILE_ERR")*(C2="B36"))*(F2="GOS")=1,IF(OR(D2="",D2="STR"),VLOOKUP(Data!E2,'List 2'!$A$2:$B$16,2),VLOOKUP(Data!D2,'List 1'!$A$2:$B$7,2)),""),"")

Tkbuc - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

SUMPRODUCT is just one way of checking logical condition (often used on array/range).

First, MATCH function looks for exact match for Form in list. If not found, it returns error.
So, if any number is returned, it means match has been found in the list. Therefore, ISNUMBER is used to convert that into True/False array.

Multiplication of logical statement equates to AND(Logical1, Logical2, ...).

Addition of logical statement equates to OR(Logical1, Logical2, ....)

This is because in many programs (including Excel). True = 1 and False = 0.

So above, can also be written as...
IF(AND(AND(ISNUMBER(MATCH(D85,'GOSC Docs'!$A$2:$A$16,0)),OR(D85<>"SMILE_ERR",AND(D85="SMILE_ERR",OR(O85="B07",O85="B36")))),(AK85="GOSC"),,)

I just find it easier to read/write logical statement in mathematical terms, rather than in multiple nested AND/OR statements.

But, to think of it... Since we are not trying to evaluate array of true/false, there's no need for SUMPRODUCT in this case. Formula can be written as...

=IFERROR(IF((ISNUMBER(MATCH(D85,'GOSC Docs'!$A$2:$A$16,0))*(D85<>"SMILE_ERR")+(D85="SMILE_ERR")*(O85="B07")+(D85="SMILE_ERR")*(O85="B36"))*(AK85="GOSC")=1,IF(OR(P85="",P85="STR"),VLOOKUP(AF85,'GOSC SM'!$C$2:$H$32,6,0),IFERROR(VLOOKUP(P85,'GOSC RM'!B:F,5,0),VLOOKUP(AF85,'GOSC SM'!$C$2:$H$32,6,0))),""),"")

***bump****