lookup logic to interrogate specific exceptions

[bdav1216]

Hello,


See the below data example.


Purpose: Output a 'Y' or 'N' based on a look-up. If the data is found in the columns in the look-up, a 'Y' is output, else 'N'.


This is probably a IF/THEN type logic, but your assistance is greatly appreciated!


Logic/Rules:


1) If the look-up matches 'Account Name' in Column A of the 'Data' tab to 'Account Name' in the 'ReClass' tab, output a 'Y'. If the value of 'Y' is matched, no other work is required, but if the look-up doesn't find a match, the values of 'ID' and 'VER' need to be checked.


Examples:
- Row 2 outputs a 'Y' because 'Account Name' is found in the look-up column. When this occurs, no additional checks are needed.
- Row 3 would output a ' N' because 'Account Name' is NOT found in the look-up column. When this occurs, move on the to the next checks. The 'ID' or 'VER' is not found either so a value of 'N' is output.
- Row 4 would output a 'Y' because 'Account Name' is NOT found in the look-up column. When this occurs, move on the to the next checks. The 'ID' was found so a value of 'Y' is output.


When the first search doesn't find a match, check.....
It is possible to have a 'Y' value when the 'ID' and 'VER' fields have values found in the look-up, however, there will be situations where the 'VER' field is found in the look-up, but the 'ID' is not, and thus, outputs a 'N' value so these need to be checked independently else this would over count.


2) IF Column B ('ID') in 'Data' tab is found in the look-up in Column B in the 'ReClass' tab, output a 'Y' in Column D in the 'Data' tab. If it's not found, move to the next set of logic for a 2nd chance.


Examples:
- Account Name was not found in the look-up, but Row 5 of the 'Data' tab matches on Column B (ID) to Column B of the 'ReClass' tab. Since this value matches, the evaluation is complete and a 'Y' is output in Column D of the 'Data' tab.
- Account Name was not found in the look-up, but Row 7 of the 'Data' tab matches on Column B (ID) in 'Data' tab. Since this value matches, the evaluation is complete and a 'Y' is output in Column D of the 'Data' tab.
- Row 6 and 8 had different ID values that were not found in the look-up so output a 'N' value.


3) Check Column C (Ver) column. If this field's value is found in the lookup, a 'Y' is output in Column C of the 'Data' tab.


Examples:
- Row 9 output a 'Y'. Although the 'ID' field (Column B) is blank and the account name also didn't match, Column C ('Ver') is found in the look-up.
- Row 10 outputs a 'Y'. Although the 'ID' field (Column B) is present it isn't found in the look-up and the account name also didn't match. However, Column C ('Ver') is found in the look-up.
- Row 11 outputs a 'N'. Neither the Account Name and 'ID' field are found in the look-up. Column C ('Ver') is also not found in the look-up.


Data Tab:

[TABLE="width: 595"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Account Name[/TD]
[TD]ID[/TD]
[TD]VER[/TD]
[TD]Match[/TD]
[/TR]
[TR]
[TD]ATT[/TD]
[TD]ATT01[/TD]
[TD]XXX[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]SNAP[/TD]
[TD]SNAP01[/TD]
[TD]XXX[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]TRAP[/TD]
[TD]TRAP01[/TD]
[TD]XXX[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]CIT111[/TD]
[TD]XXX[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]STAT[/TD]
[TD]STAT111[/TD]
[TD]YYY[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]STAT[/TD]
[TD]STAT112[/TD]
[TD]YYY[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]STAT[/TD]
[TD]STAT113[/TD]
[TD]YYY[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]TOB[/TD]
[TD] [/TD]
[TD]COM[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]TOB[/TD]
[TD]TOB1A[/TD]
[TD]COM[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]TAT[/TD]
[TD]TOB1C[/TD]
[TD]XXX[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]ATT[/TD]
[TD]STAT112[/TD]
[TD]COM[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]ATT[/TD]
[TD]STAT113[/TD]
[TD]COM[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]ATT[/TD]
[TD]STAT113[/TD]
[TD]XXX[/TD]
[TD]Y

[/TD]
[/TR]
</tbody>[/TABLE]



ReClass Tab:

[TABLE="width: 562"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Account Name[/TD]
[TD]ID[/TD]
[TD]VER[/TD]
[/TR]
[TR]
[TD]ATT[/TD]
[TD]TRAP01[/TD]
[TD]ZZZ[/TD]
[/TR]
[TR]
[TD]BCD[/TD]
[TD]CIT111[/TD]
[TD]YYY[/TD]
[/TR]
[TR]
[TD]ECF[/TD]
[TD]STAT112[/TD]
[TD]COM[/TD]
[/TR]
[TR]
[TD]DGDE[/TD]
[TD]TOB1D[/TD]
[TD]BXZ[/TD]
[/TR]
[TR]
[TD]DRDS[/TD]
[TD]TRAP02[/TD]
[TD]ZXB[/TD]
[/TR]
[TR]
[TD]DED[/TD]
[TD]TRAP03[/TD]
[TD]AZF[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.excelforum.com/excel-formulas-and-functions/1285213-lookup-logic-statement-needed-to-evaluate-multiple-conditions.html#post5169374

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.