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.

20 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

@Sergei Baklan I have been working on a very similar problem for hours now. My formula is: =IFERROR(INDEX(Sheet1!B:B,MATCH(MemberData!$A$44,Sheet1!$G$2:$G$3348,0)),"") trying to emulate what you did. I am trying to search for my numbers in Member Data sheet column A to see if they are in Sheet 1 column B, if they are, I want the emails from sheet 1 column G to fill into the corresponding column in the member data sheet (this case they would be in F which is where I was housing my formula) Please save us!! Lol I have also used:  =IFERROR(INDEX(Sheet1!$B$1:$B$3347,MATCH(MemberData!$A$44,Sheet1!$B$1:$B$3347,0)),"") to no avail


Perhaps it shall be

=IFERROR( INDEX( Sheet1!$G$1:$G$3347, MATCH( MemberData!$A$44, Sheet1!$B$1:$B$3347,0)),"")

@Sergei Baklan  Can you help me with my issue too? 

I only want to display companies from sheet one on sheet 2 that have a match for cell A1 on sheet 2.


Sheet 1 last column, =IF(ISNA(MATCH(C2,{"abc","abc1","abc2","abc3"},0)),"-",MAX(I$1:I1)+1)

Dateserial numbercompany  Review Date Status 
5/1/2018B12345ABC  5/1/2018 Accepted1
5/2/2018B23456ABC1  5/2/2018 Consider2
5/3/2018B34567ABC2  5/3/2018 Compare Elsewhere3
5/4/2018B45678ABC3  5/4/2018 Rejected4
5/5/2018C12345ABC  5/4/2018 Accepted5
5/5/2018C23456ABC1  5/2/2018 Consider6
5/5/2018D12345ABC2  5/3/2018 Compare Elsewhere7
5/5/2018D24567ABC3  5/4/2018 Rejected8


Sheet 2

Each data point in all three columns

=IFERROR(INDEX('Sheet 1'!A:A,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")

=IFERROR(INDEX('Sheet 1'!B:B,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")

=IFERROR(INDEX('Sheet 1'!C:C,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")


dateserial numberCompany


Thank you in advance.


Hello, I'm needing assistance in doing a similar formula as the others on this. I'm using an excel work book, Sheet named FW.Model column AA5 and AB5, I need to look up on the Sheet named Page 1, Columns B1:C357, if match return value in column E (from same tab - Page 1) 5th column over.

Basically if the manufacture and model match the manufacture model on the second tab, return a value in the 5th column on the second tab.

Thank you in advance for your help.
Hi @HMills8475 I am attempting almost similar but cannot get it to work. I hope you can notice this and help.

If the value in column B is in Sheet 1, column I, return x. But if the value in column B is in Sheet 2, column c, return y.

I believe I have a similar problem.


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 data points for corresponding name.


If the name in Sheet 1, Column B matches the name in Sheet 2, Column A and the date in Sheet 1, Column C matches the date in Sheet 2, Column C then the data in Sheet 2, Column B needs to be added to Sheet 1, Column A.


Each listing of a name comes from a different month in which data was pulled. The month is listed in Sheet 1, Column C.


That being said, each data point is different and needs to match up with the name and month when added to Sheet 1, Column A.