Plusieurs Colonnes de vs Multiples de la Colonne de Recherche

Je suis après une formule pour correspondre à un certain nombre de colonnes entre deux feuilles de calcul et de retour de la dernière référence des feuilles de calcul final de données de la colonne. Je sais que c'est faisable en VBA, mais suis à la recherche d'une méthode de la formule.

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

La formule que je suis après doivent correspondre à chaque colonne de cette ligne particulière et d'ajouter de la cellule de Données de la valeur de la ReferenceWorksheet à la MainWorksheet.

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

Je n'ai pas trouvé un cleancut façon de le faire à l'aide de plusieurs colonnes à l'aide de RECHERCHEV, INDEX(MATCH)) etc. Est-il un moyen de filtre à l'intérieur d'une fonction?

Toute aide est très appréciée!

OriginalL'auteur Matt Rowles | 2012-01-30

excel excel-formula

8

Je suis d'accord avec vasek1, d'ajouter des colonnes supplémentaires permettra de simplifier les formules nécessaires, mais si vous voulez éviter de colonnes qu'il y a [relativement] méthodes simples disponibles.

Méthode 1 - faire la même concaténation comme vasek1....mais à l'intérieur de la formule, par exemple, dans E2
Principal

=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))

formule doit être confirmé avec la touche CTRL+MAJ+ENTRÉE

Méthode 2 - un de tableau non version avec RECHERCHE

=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)

Noter que la première formule trouve le première match, le dernier de la dernier. Je suppose que les données de référence n'ont qu'une seule instance de chaque région/pays/de la ville de combinaison dans ce cas, ils seront tous les deux donnent le même résultat, mais ce n'est pas garanti dans tous les cas.

Pour permettre C2 à "<>" sens "tout pays" (par commentaire) vous pouvez utiliser cette version révisée de la formule de RECHERCHE

=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)

Un changement similaire peut être appliquée à l'INDEX correspond à la version

Merci pour la réponse mate. Certains de mes lignes ont "<>" dans leur sens n'importe quel pays de cette région en particulier. Aucune idée de comment cela pourrait venir dans l'équation?
pas de problème, j'ai ajouté une autre formule pour répondre à ce scénario - je suppose que "<>" est dans la Principale colonne de feuille de calcul C
Merci s'accoupler, a bien fonctionné

OriginalL'auteur barry houdini
6

Une solution que j'utilise pour ce type de problème est de créer une colonne supplémentaire pour servir d'identificateur unique pour chaque table. Donc, dans votre cas,

Table principale: formule pour la clé, en supposant que vous commencez avec la colonne 1 = Un, est
```
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]
```
Table de référence: ici, insérer la colonne à gauche, de sorte que vous pouvez faire une recherchev sur elle. Formule Clé en A2 est
```
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
```
Alors, la formule de recherche dans la table principale devient très simple:
```
F2 = VLOOKUP(E2, ReferenceTable!$A$2:$E$4, 5, 0)
```
Ensuite, vous pouvez masquer les colonnes de clés de l'utilisateur, si nécessaire. L'avantage de cette approche est qu'il garde les formules simples et il est beaucoup plus facile à comprendre et à mettre à jour qu'à la rédaction de VBA ou une formule compliquée.

Merci pour la réponse mate. Certains de mes lignes ont "<>" dans leur sens n'importe quel pays de cette région en particulier. Aucune idée de comment cela pourrait venir dans l'équation?
Excellente solution. Celui qui vous fait vous demander "Pourquoi n'ai-je pas pensé à ça!?" xD

OriginalL'auteur vasek1
0

Voici un simple exemple de multi-colonne de MATCH (le genre d'approche qui apparaît souvent lors de la recherche de ce type de formule):

Dans 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")
```
Veillez à utiliser les touches Ctrl+Maj+Entrée lors de la saisie de la formule.

L'affichage de cette note qu'il a un problème, vous devez être conscient: l'exemple ci-dessus correspond à:
```
 B | Two | Blue
```
mais il trouvera également sur:
```
 BT | wo | Blue
```
OriginalL'auteur Tim Williams
0

Ce que vous avez besoin est généralement appelé un Plusieurs Recherche.
Cela a été demandé à quelques reprises, sous des formes diverses.
J'ai compilé ici une liste de ces postes.
(Celui-ci est dans la liste)

Il y a de nombreuses solutions possibles pour que.
Celui que j'ai trouvé le plus robuste est montré ici.
Adapté à ce cas, la formule de la E3 serait
```
=INDEX(Ref!D:D,SUMPRODUCT(--(Ref!A:A=B3),--(Ref!B:B=C3),--(Ref!C:C=D3),ROW(Ref!D:D)),0)
```
et copier vers le bas.

OriginalL'auteur sancho.s

Vous devez vous connecter pour publier un commentaire.