0

I have a small Excel task that I am stuck on.

I have two Excel tables and would like to fill table 1 with values from table 2.

Example:

Table1:

A B C D E
1 Dude
2

Table2:

A B C D E
1 Max Muster Way 3 53
2 Dude Test Street 42 44

The formula should now copy the value from Table2 B2 in Table 1 for B1. This means that the formula must find the correct row in table 2 using the value in table 1 A1 and copy the value from column B.

Does anyone have any ideas?

TheDude
  • 89
  • 7

1 Answers1

1

An easy way would be using INDEX and MATCH:

enter image description here

My formula in column I is:

=INDEX($B$1:$E$2;MATCH($H1;$A$1:$A$2;0);1)

Notice last argument of INDEX is 1 for column I, 2 for column J, 3 for column K and so on. Depending on where is your data you may need to adapt this part. And for sure you can use another formula to autoselect proper column instead of typing 4 different formulas, but as I said, this would be the easiest way (not the cleanest, tough)

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • Thank you for the answer. I have tried it as follows, as I have two tables: =INDEX($B$1:$E$2;MATCH(Tabelle2!A1;$A$1:$A$2;0);1) Unfortunately this does not work How do I make the call for the second table so that it works? – TheDude Sep 08 '21 at 09:54