Lookup or match values in a table over two columns, and return a third value

[Specialpatrolgroup]

Hi all, Think I may be missing the obvious here... I have an Excel workbook with a static data table (Table A) with two sets of data, columns A & B

A Town B Category
DERBY Acquisition
WATFORD Implementation
NORTHAMPTON Disinvest
NOTTINGHAM Implementation
NORWICH Implementation
BIRMINGHAM Acquisition

In a separate Raw Data Excel sheet I have Data sets with 'Towns' (Columns A & B) spread over 2 columns. I want to match/find/lookup Towns from the static data table (Table A) in my Raw Data sheet, and Return the corresponding Category from Table A into the Raw Data set column C EG:

A Towns B Towns 2 C Category
DERBY BRIGHTON Acquisition
LIVERPOOL WATFORD Implementation
NORWICH Implementation
NOTTINGHAM CREWE Implementation
BIRMINGHAM Acquisition
BIRMINGHAM Acquisition

The Raw Excel Sheet will have duplicates of the towns in columns A and B, There will also be blank cells in A & B, and other 'Towns' not in my static data table (Table A)

Hope that is clear what I need, I'm looking for a formula not code etc. cheers for any help, and again apologies if this is an obvious one....

 

[Fluff]

What should be returned if you have Derby & Watford on the same row in the raw sheet?

 

[Specialpatrolgroup]

What should be returned if you have Derby & Watford on the same row in the raw sheet?

Hey Fluff, that should not happen, due to the Towns 1 & 2 (Columns A&B) always being different in the Raw Data.... but good question, if it did occur, I'd want 'Category Duplication' returned if possible. Again, should not happen given the data I am looking at, but if that could be built-in, then lovely!

 

[Fluff]

Ok, how about

=IFNA(INDEX('Sheet 1'!$B$2:$B$100,MATCH(A2,'Sheet 1'!$A$2:$A$100,0)),IFNA(INDEX('Sheet 1'!$B$2:$B$100,MATCH(B2,'Sheet 1'!$A$2:$A$100,0)),""))

 

[Specialpatrolgroup]

Ok, how about

=IFNA(INDEX('Sheet 1'!$B$2:$B$100,MATCH(A2,'Sheet 1'!$A$2:$A$100,0)),IFNA(INDEX('Sheet 1'!$B$2:$B$100,MATCH(B2,'Sheet 1'!$A$2:$A$100,0)),""))

Think that has nailed it, many thanks!

 

[Fluff]

You're welcome & thanks for the feedback.