Multiple Column vs Multiple Column Lookup

Question

I am after a formula to match a number of columns between two worksheets and return the last reference worksheets final column data. I know this is doable in VBA, but am looking for a formula method.

MainWorksheet:

User  | Region   | Country | City | Lookup
--------------------------------------------------
User1 | Europe   | Italy   | Rome | [formula here]
User2 | Americas | Brazil  | Rio  | [formula here]

ReferenceWorksheet:

Region   | Country | City   | Data
-----------------------------------
Europe   | England | London | some data
Americas | Brazil  | Rio    | more data
Europe   | Italy   | Rome   | some more data

The formula I am after should match each column in that particular row and add the Data cell value from the ReferenceWorksheet to the MainWorksheet.

eg. If (MainWorksheet.Region = ReferenceWorksheet.Region) &&
       (MainWorksheet.Country == ReferenceWorksheet.Country) && 
       (MainWorksheet.Region == ReferenceWorksheet.Region) Then
    MainWorksheet.Column E = ReferenceWorksheet.Current Row:Data Column

I haven't found a cleancut way to do this using mutliple columns using VLOOKUP, INDEX(MATCH)) etc. Is there a way to filter within a function?

Any help is much appreciated!

Solution 1

I agree with vasek1, adding additional columns will simplify the formulas required but if you want to avoid extra columns there are [relatively] simple methods available.

Method 1 - do the same concatenation as vasek1....but within the formula, e.g. in E2 Main

=INDEX(Ref!D$2:D$100,MATCH(B2&"-"&C2&"-"&D2,Ref!A$2:A$100&"-"&Ref!B$2:B$100&"-"&Ref!C$2:C$100,0))

formula needs to be confirmed with CTRL+SHIFT+ENTER

Method 2 - a non-array version with LOOKUP

=LOOKUP(2,1/(Ref!A$2:A$100=B2)/(Ref!B$2:B$100=C2)/(Ref!C$2:C$100=D2),Ref!D$2:D$100)

Note that the first formula finds the first match, the latter the last. I assume that the reference data will only have a single instance of each region/country/city combination in which case they will both give the same results, but that isn't guaranteed in every situation.

To allow C2 to be "<>" meaning "any country" (as per comment) you can use this revised version of the LOOKUP formula

=LOOKUP(2,1/(Ref!A$2:A$100=B2)/((Ref!B$2:B$100=C2)+(C2="<>"))/(Ref!C$2:C$100=D2),Ref!D$2:D$100)

A similar change can be applied to the INDEX/MATCH version

Solution 2

A solution I use for this type of problem is to create an extra column to serve as the unique identifier for each table. So, in your case,

Main table: formula for key, assuming you start with column 1 = A, is

E2 = B2 & "(underscore)" & C2 & "(underscore)" & D2

User  | Region   | Country | City | Key                 | Lookup        
--------------------------------------------------
User1 | Europe   | Italy   | Rome | Europe_Italy_Rome   | [formula here]
User2 | Americas | Brazil  | Rio  | Americas_Brazil_Rio | [formula here]

Reference table: here, insert the extra column to the left so you can do a vlookup on it. Formula for Key in A2 is

A2 = B2 & "(underscore)" & C2 & "(underscore)" & D2


Key                   | Region   | Country | City   | Data
---------------------------------------------------------------------
Europe_England_London | Europe   | England | London | some data
Americas_Brazil_Rio   | Americas | Brazil  | Rio    | more data
Europe_Italy_Rome     | Europe   | Italy   | Rome   | some more data

Then, the lookup formula in the main table becomes very simple:

F2 = VLOOKUP(E2, ReferenceTable!$A$2:$E$4, 5, 0)

You can then hide the key columns from the user, if necessary. The advantage of this approach is that it keeps the formulas simple and is much easier to understand and update than writing VBA or a complicated formula.

Comments

[Matt Rowles]

Thanks for the reply mate. Some of my rows have "<>" in them meaning any country in that particular region. Any idea how that could come into the equation?

[Matt Rowles]

Thanks for the reply mate. Some of my rows have "<>" in them meaning any country in that particular region. Any idea how that could come into the equation?

[barry houdini]

@Bonjour no problem, I added another formula to cater for that scenario - I assume that "<>" is in the Main worksheet column C

[IDIR Samir]

Brilliant solution. One that makes you wonder "Why didn't I think of that!?" xD