Match Cells on Sheet 1 & 2 and return value from a cell on Sheet 2 to a new cell on Sheet 1

New Contributor

Sheet 1, Column B lists names. Same names are listed multiple times. Sheet 2, Column A lists one name and Sheet 2, Column B lists phone # for corresponding name.


If the name in Sheet 1, Column B matches the name in Sheet 2, Column A then the phone # in Sheet 2, Column B needs to be added to Sheet 1, Column A.


I've attached a test workbook to help demonstrate.


Can I do this with a formula? Or do I need a macro? I've been trying different formulas and can't seem to get it to work.

12 Replies

Hi Katie,


That could be like


and attached

That did it! I can't thank you enough!!!

@Sergei Baklan I'm attempting the same thing but cannot get the formula to work. If sheet 1 column J to match sheet 2 column G then return the value of column E from sheet 2 onto sheet 1 column L. 

At what part of the formula does it look at column E from sheet 2 and add to sheet 1? 
Thank you, 


MATCH(J1, Sheet2!G:G, 0) returns first found position in Sheet2!G:G where the value is equal to value of J1, or error if nothing was found.

INDEX(Sheet2!E:E, <above position>) returns the value of the cell in column Sheet2!E:E which is on that position.

All together

=IFERROR( INDEX ( Sheet2!E:E, MATCH( J1, Sheet2!G:G, 0) ), "nothing found")

@Sergei Baklan Thank you!!! I could literally cry, I'm so happy it worked Bless you!! 

@Sergei Baklan I have a requirement like - I need to check the values of 2 different cells in Sheet 1 with master data in sheet 2 and if they match, then I need to display the $ values from master data on a cell in sheet 1. If (Sheet1.Resource Location.value AND Sheet1.Resource Type.value) = (Sheet2.Resource Location.value AND Sheet2.Resource Type.value) then sheet1.Hourly Rate.value =Sheet2.HourlyRate.value (This should be checked across the master values to see if the shee1 data combination has an entry to return the $rate).


Depends on your Excel version you may use

=XLOOKUP(A2&B2,Sheet2!$A$2:$A$46&Sheet2!$B$2:$B$46,Sheet2!$C$2:$C$46,"no such")


=IFERROR( INDEX( Sheet2!$C$2:$C$46, MATCH( A2&B2, Sheet2!$A$2:$A$46&Sheet2!$B$2:$B$46,0 ) ), "no such")

Much better if you transform source data into structured tables (Ctrl+L), with that formulas could be

  [@[Resource Location]] & [@[Resource Type]],
  Table2[Resource Location] & Table2[Resource Type],
  Table2[Hourly Rate],"no such")


   INDEX( Table2[Hourly Rate],
             [@[Resource Location]] & [@[Resource Type]],
             Table2[Resource Location] & Table2[Resource Type],
             0 )
 "no such")

@Sergei Baklan This is simply awesome. Thank you very much for your shift turnaround and a great solution - much appreciated.

This work great is there a way to bring over the hyperlink as well