Jun 16 2022 01:08 PM
Hello,
I have two sheets in one workbook. One that has data that contains birthdays and a shared ID number between the two sheets. I reference this ID as column one in the formulas. The other sheet has the same ID numbers and staff names in it but the data is repeated multiple times. I am having trouble getting the VLOOKUP to repeat the data for the matches. For example, I need the data in sheet one to repeat in sheet two for each ID match. Do I have to make sure the birthday data is formatted a certain way?
Any ideas, or am I using the wrong formula?
Thanks in advance!
Sheet with Master Data (sheet1)
ID | Name | Birthday |
1234 | John Smith | 11/24/1998 |
4321 | Jane Doe | 01/04/1995 |
Destination Sheet 2
ID | Name | Birthday (Need BD repeated in this column from sheet 1) |
1234 | John Smith | |
1234 | John Smith | |
1234 | John Smith | |
4321 | Jane Doe | |
4321 | Jane Doe |
Jun 16 2022 01:15 PM
=VLOOKUP(A3&B3,CHOOSE({1,2},sheet1!$A$2:$A$3&sheet1!$B$2:$B$3,sheet1!$C$2:$C$3),2,FALSE)
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Jun 17 2022 06:37 AM
Jun 17 2022 02:26 PM
=VLOOKUP(A2&B2&C2&D2,CHOOSE({1,2},$A$15:$A$19&$B$15:$B$19&$C$15:$C$19&$D$15:$D$19,$E$15:$E$19),2,0)
You can try this formula for example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. You can as well apply INDEX and MATCH.
Basically the CHOOSE function allows to create an individual search and return matrix within VLOOKUP.
A2&B2&C2&D2 The search string is "101employee1company1north" in the example.
$A$15:$A$19&$B$15:$B$19&$C$15:$C$19&$D$15:$D$19 is the search matrix {"103employee3company3east";"104employee4company4mid";"101employee1company1north";"102employee2company2south";"105employee5company5west"} in the example.
VLOOKUP checks if the search string is found in the matrix. If there is an exact match the value of the corresponding row of the return matrix $E$15:$E$19 is shown.
In the formula bar you can select (for example) the search matrix and press F9 to show the content of this matrix. You can press ctrl+Z to return to the original formula.