Sep 01 2018 06:39 AM
Sep 01 2018 06:39 AM
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.
Sep 01 2018 09:15 AM
That could be like
Sep 10 2021 08:57 AM
@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?
Sep 10 2021 11:12 AM
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.
=IFERROR( INDEX ( Sheet2!E:E, MATCH( J1, Sheet2!G:G, 0) ), "nothing found")
Sep 13 2021 05:18 AM
@Sergei Baklan Thank you!!! I could literally cry, I'm so happy it worked Bless you!!
Sep 30 2021 11:55 AM
@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).
Sep 30 2021 01:18 PM
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")
=IFERROR( INDEX( Table2[Hourly Rate], MATCH( [@[Resource Location]] & [@[Resource Type]], Table2[Resource Location] & Table2[Resource Type], 0 ) ), "no such")
Sep 30 2021 05:54 PM
@Sergei Baklan This is simply awesome. Thank you very much for your shift turnaround and a great solution - much appreciated.
Oct 01 2021 01:46 AM
@selvamohanchinnasamy , you are welcome
Oct 19 2021 09:41 AM