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

=IFERROR(INDEX(Sheet2!$B$2:$B$10,MATCH(Sheet1!$B2,Sheet2!$A$2:$A$10,0)),"")

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, 

@HMills8475 

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).

@selvamohanchinnasamy 

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")

or

=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

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

or

=IFERROR(
   INDEX( Table2[Hourly Rate],
          MATCH(
             [@[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