Lookup Data table columns A and B and compare column C

[dannypwebb]

I have my data table that looks like this:

A B C
1 Country Code Business Unit Archive location
2 AE 0011 CH
3 AE 0012 CH
4 AE Others GB
5 AR 0012 CH
6 AR Others US
7 AT All CH
8 AU All SG
9 BE All CH
10 BH 0012 CH
11 BH 0009 CH
12 BH Others GB



This is the data I need to find a formula to lookup the above data table under Column A AND B and check for match in Column C:

A B C D
1 Country BU Region Formula here
2 AE 0011 CH
3 AE 0006 GB
4 AE Other CH
5 AU All SG
6 BH 0012 CH
7 BH 0019 GB



Example 1:
1. Cell A2 to Lookup Data Table in column A
a. If Result found:
1. B2 to lookup Data Table in Column B
a. If B2 is a match, check the value in C2 against Column C in Data Table and report if match or not
b. If B2 is not a match, check the value in Column C in Data Table against either 'Other' or 'All'
b. If no match, report not found


I hope I have explained the requirements clearly enough. Lookup A2 against Column A in Data Table. If a match, check the value in B2 against Column B in Data Table. Report if C2 is a match to Column C in Data Table

Any help would be much appreciated indeed.

Thank you

Danny

[oeldere]

HOW TO ATTACH YOUR SAMPLE WORKBOOK:
Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.