Hello,
I'm trying to use Excel to compare two columns in a table, table 1, with two other column from another table, table 2, in a sheet. If the values match then copy the value in a cell of the same row of another column of table 2 into table 1. In other word, Im trying to copy value of a 3rd column from this table into another table if the values in 2 groups of two different columns in 2 table match. Sorry for my bad English. I come up with a solution named "INDEX and MATCH with multiple criteria" from ExcelJet.net at this address:
The solution worked for me but there is one thing I don't understand. Its row 0 of an array.[http-colon-slash-slash]exceljet[dot]net/formula/index-and-match-with-multiple-criteria
To illustrate the situation, I have made this table. Suppose I have 3 columns in the table 1 named title, year and imdb respectively and 3 columns named title 2, year 2, imdb 2 respectively in table 2. Imdb 2 column in table 2 is filled with something like the imdb info links of the movies with title and released year in the title 2, year 2 columns of the same table. Now table 1 also contains something similar like table 2 with title and released year of the movies but not the imdb link. The content of the two table is out of order and similar but not the same. I want to check the title and released year of the movies in table 1 with the title and released year of movies in table 2. If there is a match then copy the imdb info link of the movie from table 2 to table 1.
My table is look like this.
index-match-multiple-criteria.jpg
Column A, B, C contain the thing for table 1. Column D, E, F contain the thing for table 2. I want to copy the thing from column F to column C if the cells in column D, E matched with the thing in column A, B. I have this formula to put into the cellss in column C:[A] [B] [C] [D] [E] [F]
[title][year][imdb][title 2][year 2][imdb 2]
[a2][b2][c2][d2][e2][f2]
[a3][b3][c3][d3][e3][f3]
...................
This formula is taken from the general formula on the Excel Jet website like:=INDEX($D$2:$F$14,MATCH(1,INDEX((A2=$D$2:$D$14)*(B2=$E$2:$E$14),0,0),0),3)
But there one thing I didn't understand. What is row 0 in the formula INDEX((A1=range2)*(B1=range3),0,1). Why isn't it be row 1 or something. Yes, ofcourse for row 1 column 1 it didn't work but I have checked it with row 0, column 0 and it worked like row 0, column 1 in the formula, but I don't understand. Can anyone please explain a bit? This is the text got from ExcelJet:=INDEX(range1,MATCH(1,INDEX((A1=range2)*(B1=range3),0,1),0))
excel-jet-index-match.jpgNon-array version
It is possible to add another INDEX to this formula, avoiding the need to enter as an array formula with control + shift + enter:
The INDEX function can handle arrays natively, so the second INDEX is added only to "catch" the array created with the boolean logic operation and return the same array again to MATCH. To do this, INDEX is configured with zero rows and one column. The zero row trick causes INDEX to return column 1 from the array (which is already one column anyway).Please Login or Register to view this content.
Thank you in advance.
Bookmarks