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

%3CLINGO-SUB%20id%3D%22lingo-sub-241172%22%20slang%3D%22en-US%22%3EMatch%20Cells%20on%20Sheet%201%20%26amp%3B%202%20and%20return%20value%20from%20a%20cell%20on%20Sheet%202%20to%20a%20new%20cell%20on%20Sheet%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241172%22%20slang%3D%22en-US%22%3E%3CP%3ESheet%201%2C%20Column%20B%20lists%20names.%20Same%20names%20are%20listed%20multiple%20times.%20Sheet%202%2C%20Column%20A%20lists%20one%20name%20and%20Sheet%202%2C%20Column%20B%20lists%20phone%20%23%20for%20corresponding%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20name%20in%20%3CSPAN%3ESheet%201%2C%20Column%20B%20matches%20the%20name%20in%20Sheet%202%2C%20Column%20A%20then%20the%20phone%20%23%20in%20Sheet%202%2C%20Column%20B%20needs%20to%20be%20added%20to%20Sheet%201%2C%20Column%20A.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI've%20attached%20a%20test%20workbook%20to%20help%20demonstrate.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECan%20I%20do%20this%20with%20a%20formula%3F%20Or%20do%20I%20need%20a%20macro%3F%20I've%20been%20trying%20different%20formulas%20and%20can't%20seem%20to%20get%20it%20to%20work.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-241172%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241443%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20Cells%20on%20Sheet%201%20%26amp%3B%202%20and%20return%20value%20from%20a%20cell%20on%20Sheet%202%20to%20a%20new%20cell%20on%20Sheet%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241443%22%20slang%3D%22en-US%22%3E%3CP%3EKatie%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241187%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20Cells%20on%20Sheet%201%20%26amp%3B%202%20and%20return%20value%20from%20a%20cell%20on%20Sheet%202%20to%20a%20new%20cell%20on%20Sheet%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241187%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20did%20it!%20I%20can't%20thank%20you%20enough!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-241184%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20Cells%20on%20Sheet%201%20%26amp%3B%202%20and%20return%20value%20from%20a%20cell%20on%20Sheet%202%20to%20a%20new%20cell%20on%20Sheet%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-241184%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Katie%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Sheet2!%24B%242%3A%24B%2410%2CMATCH(Sheet1!%24B2%2CSheet2!%24A%242%3A%24A%2410%2C0))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eand%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
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