Multiple column versus multiple column search

Question

I get a formula to match the number of columns between two sheets and return the latest column data of the link table. I know this is doable in VBA, but I'm looking for a formula method.

Mainworksheet:

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

Reference Worksheet:

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

The formula I should then correspond to each column in this particular row and add the value of the data cell from the reference sheet to the main sheet.

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

I did not find a cleancut way to do this using mutliple columns, using VLOOKUP, INDEX (MATCH), etc. Is there a way to filter inside a function?

Any help is much appreciated!

Answer 1

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

Method 1 - perform the same concatenation as vasek1 .... but within the limits of the formula, for example. at E2 Home

=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

must be confirmed with CTRL + SHIFT + ENTER

Method 2 - version without array 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 matches first , the last one last . I assume that in the reference data there will be only one copy of each combination of regions / countries / cities, in which case they will give the same results, but this is not guaranteed in every situation.

So that C2 is "<>" , which means "any country" (according to the 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.

Answer 2

The solution I use for this type of problem is to create an extra column that will serve as a unique identifier for each table. So in your case

Main table: formula for the key, if you start with column 1 = A,

 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 an extra column to the left so you can do vlookup on it. The formula for the 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 search 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 simplifies the formulas and is much easier to understand and update than writing a VBA or a complex formula.

Answer 3

Here is a simple example of a multi-column MATCH (the kind of approach that often appears 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.

Carrying this out to note that he has a problem that you should be aware of: the example above corresponds to:

  B | Two | Blue 

but it will also match:

  BT | wo | Blue 

Answer 4

What you need is usually called multiple search. This has been asked several times, in various forms. I have compiled a list of such messages here. (This one is listed)

There are many possible solutions for this. The one I found most reliable is shown here . Adapted to this occasion, the formula in E3 will 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 down.