excel - 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!

Answer 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