Multiple Column vs Multiple Column Lookup

问题:

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!

回答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

回答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.

回答3:

Here's a simple example of multi-column MATCH (the kind of approach which often shows up when searching for this type of formula):

In E10:

=IFERROR(INDEX(E3:E5,MATCH(B10&C10&D10,$B$3:$B$5&$C$3:$C$5&$D$3:$D$5,0),1),"No Match")

Be sure to use Ctrl+Shift+Enter when entering the formula.

Posting this to note that it has an issue you should be aware of: the example above matches on:

 B | Two | Blue

but it will also match on:

 BT | wo | Blue

回答4:

What you need is typically called a Multiple Lookup. This was asked a few times, under various forms. I have compiled here a list of such posts. (This one is in the list)

There are many possible solutions for that. The one I found most robust is shown here. Adapted to this case, the formula in E3 would be

=INDEX(Ref!D:D,SUMPRODUCT(--(Ref!A:A=B3),--(Ref!B:B=C3),--(Ref!C:C=D3),ROW(Ref!D:D)),0)

and copy downward.