0

I'm not used to work in Excel. I have two excel files.

  1. In the first one I have one column with some data let's say hashed passwords.

  2. In the second file I have two columns: the first one contains password as in the first file while the second column contains other information related to the passwords.

I would like to create a sheet having the common passwords between the two files in the first column and the information on the second column (like an intersection).

My formula for the first column looks like this:

=IF(ISERROR(MATCH('path\[filename1]Sheet1'!A2;'path\[filename2]Sheet1'!$A$2:$A$106473;0));"";'path\[filename2]Sheet1'!A2)

While for the second column just the last A2 of the previous formula becomes B2. Dragging the mouse to the cells belows give me the result. Do you think is a good solution? Is there something better I can use to achieve my result?

Kamran
  • 4,010
  • 14
  • 60
  • 112
user73793
  • 151
  • 8

1 Answers1

0

Best to use the VLOOKUP function. See this SO post for a similar question and answer: Join two spreadsheets on a common column in Excel or OpenOffice

You can also use MATCH (I'm not sure if MATCH his more efficient than VLOOKUP). Let's say that Sheet1 contains:

Col A
pass1
pass8
pass3

and that Sheet2 contains:

Col A   Col B
pass1   info1
pass2   info2
pass3   info3
pass4   info4
pass5   info5
pass6   info6
pass7   info7
pass8   info8
pass9   info9
pass10  info10

Then copy the following to cell B1 in Sheet1 =INDEX(Sheet2!B1:B10,MATCH(A1,Sheet2!A1:A10,0),1) then fill down to enter the rest of the rows.

Result in Sheet1:

pass1   info1
pass8   info8
pass3   info3

Hope this helps...good luck, Paul

Community
  • 1
  • 1
pcantalupo
  • 2,212
  • 17
  • 27
  • I have actually read around that is better to use the match function for this kind of issues but didn't get into deep yet to understand why. – user73793 May 02 '14 at 16:53
  • Thank you. But my solution is wrong?...It seems to me that it works as well...but don't know about the efficiency – user73793 May 02 '14 at 17:30
  • Your solution is not wrong if it gives you the intended output in a reasonable amount of time. Try timing both the VLOOKUP and MATCH operations to see which is faster. – pcantalupo May 02 '14 at 17:46
  • I guess both solutions need quiet a lot of time being that I have a huge amount of records. The thing is that the sheet1 to which you refer has more entries that the sheet2 in my situation so if I do your way I will have a lot of N/A results which I don't want to visualize. Thanks anyway for all your suggestions; they were of great help. – user73793 May 02 '14 at 17:58