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
Hi Katie,
That could be like
=IFERROR(INDEX(Sheet2!$B$2:$B$10,MATCH(Sheet1!$B2,Sheet2!$A$2:$A$10,0)),"")
and attached
Sep 01 2018 09:49 AM
That did it! I can't thank you enough!!!
Sep 03 2018 06:36 AM
Katie, you are welcome
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?
Thank you,
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.
All together
=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 13 2021 01:24 PM
@HMills8475 , glad it helped
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")
or
=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
Dec 06 2021 08:22 PM
@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
Dec 07 2021 09:00 AM
Perhaps it shall be
=IFERROR( INDEX( Sheet1!$G$1:$G$3347, MATCH( MemberData!$A$44, Sheet1!$B$1:$B$3347,0)),"")
Dec 07 2021 12:37 PM
@Sergei Baklan I will try that, thank you so much!!
Mar 01 2022 03:06 PM
@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)
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 |
Thank you in advance.
Josie
Mar 01 2022 04:35 PM
May 12 2022 06:39 PM
May 27 2022 09:21 AM
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.