I had no idea what to Google for this. Basically I have 2 Excel files: File A and File B.

Here is File A:

File A

And here is File B:

File B

I am trying to loop through File B. I want to look at each row's first and last name columns with the first and last name columns in File A. If I find a first and last name row in File B that matches the first and last name row in File A, then I want to update File A's row for the Med_Prof value.

For example, in File B, we see that the 2nd row where last name is "Sub" and first name is "Stan" matches the 3rd row in File A where last name is "Sub" and first name is "Stan." As a result, I will update the Med_Prof number in File A with the value 11.

I am unsure how to do this aside from manually copy / pasting values. However, for spreadsheets with thousands of values, this is not feasible.

  • But what about other names if match,,, Med_prof column has other values also 6 and 25. Any single formula can't handle all such updates !! – Rajesh S Jun 2 '18 at 8:45

Try this formula:

=SUMPRODUCT((B2='[File B.xlsx]Sheet1'!$C:$C)*(C2='[File B.xlsx]Sheet1'!$B:$B),'[File B.xlsx]Sheet1'!$A:$A)

enter image description here

  • It could work, but it won't, because the numbers in column A (file B) are not stored as numbers (in the OP's screenshot). Therefore the SUMPRODUCT will fail. The OP needs to convert them to numbers first, and if it can't be done, use INDEX/MATCH instead. – piko Jun 5 '18 at 10:19

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.