Excel: Autofill Col2 cell of tableB from Col2 of tableA (if Tab 1 Col 1 matches Tab 2 Col 1)

Question

In Excel, I have one table

Say | dire
You | vous
...

and another

You | toi
Say | dire
...

Since You in the first table matches You in the second one, the value toi should automatically become vous, that is the second table acquire values from the first one. How to do so?

Answer 1

Another option is to use index-match formulas. These are not as volatile as Vlookup, and don't require anything to be formatted in a particular manner or alphabetized.

Index match is a nested formula - consisting of an index formula which references a match lookup. Assuming that the reference table is at site A1-B5, and the table you want to create is at D1-E5 with a lookup source in row D, this is what the formula should look like in cell E1.

=index($B$1:$B$5,Match(D1,$a$1:$a$5,0))

As a two-part formula, the way it works is as follows:

The index formula returns the entry for column B that the Match formula looks for

The match formula looks for a result in column A that matches the entry in d1, and returns the row number. the 0 denotes an exact match.

Answer 2

You can use a VLOOKUP to do this. Let's say your first table starts in cell A1, and your second table starts in cell F1. Enter this formula in the second column of your second table, and copy down:

=VLOOKUP(G1,$A$1:$B$99,2,0)