Combination of INDEX and MATCH doesnt work.

[lenzwagner105]

Hi guys I need your help. For work I need to compare two different files and compare columns and their values in each of these files and if they match, spit out a matching value.

I have "tried" the principle on a basic dataset (about 20 rows) and there it has always worked without problems, but when I apply this function to my relevant dataset (one file has about 300 rows, the other about 20,000), no matches come out, even if I create them manually on purpose. I used the following command.

=IFERROR(INDEX(Tabelle3[Spalte3],MATCH(Tabelle3[Spalte1]&Tabelle3[Spalte2],Tabelle4[Spalte1]&Tabelle4[Spalte2],0)),"Fehler")

Do you guys have any idea why it works once and not once? Possibly formatted incorrectly? I would be very happy about an answer.

[#2]

Have you double-checked both tables for things like leading and trailing spaces?
Also, to avoid false positives, I always use a separator character between the columns:
MATCH(Tabelle3[Spalte1]&"|"&Tabelle3[Spalte2],Tabelle4[Spalte1]&"|"&Tabelle4[Spalte2],0)

[#3]

@lenzwagner105 

=IFERROR(IF(NOT(ISNA(MATCH(Tabelle1[@spalte1]&Tabelle1[@spalte2],Tabelle2[spalte1]&Tabelle2[spalte2],0))),INDEX(Tabelle1[spalte3],MATCH(Tabelle1[@spalte1]&Tabelle1[@spalte2],Tabelle1[spalte1]&Tabelle1[spalte2],0)),"Fehler"),"Fehler")

You can try this formula in the attached file. It's only a guess because i'm not sure what you actually want to do. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.