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

Copper Contributor

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

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.

29 Replies

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

Hi Katie,

That could be like

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

and attached

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

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

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

Katie, you are welcome

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

@SergeiBaklan 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,

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

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

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

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

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

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

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

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

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

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

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

@selvamohanchinnasamy , you are welcome

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

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

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

@SergeiBaklan 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

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

Perhaps it shall be

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

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

@SergeiBaklan I will try that, thank you so much!!

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

@SergeiBaklan  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)

 Date serial number company Review Date Status 5/1/2018 B12345 ABC 5/1/2018 Accepted 1 5/2/2018 B23456 ABC1 5/2/2018 Consider 2 5/3/2018 B34567 ABC2 5/3/2018 Compare Elsewhere 3 5/4/2018 B45678 ABC3 5/4/2018 Rejected 4 5/5/2018 C12345 ABC 5/4/2018 Accepted 5 5/5/2018 C23456 ABC1 5/2/2018 Consider 6 5/5/2018 D12345 ABC2 5/3/2018 Compare Elsewhere 7 5/5/2018 D24567 ABC3 5/4/2018 Rejected 8

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

 ABC date serial number Company 5/1/2018 B12345 ABC 5/2/2018 B23456 ABC1 5/3/2018 B34567 ABC2 5/4/2018 B45678 ABC3 5/5/2018 C12345 ABC 5/5/2018 C23456 ABC1 5/5/2018 D12345 ABC2 5/5/2018 D24567 ABC3

Josie

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

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.

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

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.

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

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.