lookup logic to interrogate specific exceptions

bdav1216

New Member
Joined
Mar 19, 2016
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
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:

Account NameIDVERMatch
ATTATT01XXXY
SNAPSNAP01XXXN
TRAPTRAP01XXXY
AAACIT111XXXY
STATSTAT111YYYN
STATSTAT112YYYY
STATSTAT113YYYN
TOB COMY
TOBTOB1ACOMY
TATTOB1CXXXN
ATTSTAT112COMY
ATTSTAT113COMY
ATTSTAT113XXXY


<colgroup><col><col><col><col></colgroup><tbody>
</tbody>



ReClass Tab:

Account NameIDVER
ATTTRAP01ZZZ
BCDCIT111YYY
ECFSTAT112COM
DGDETOB1DBXZ
DRDSTRAP02ZXB
DEDTRAP03AZF

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,127,958
Messages
5,627,850
Members
416,276
Latest member
kbhoops15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top