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

12 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

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

# 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

@Sergei Baklan 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

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

# 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

@Sergei Baklan 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