Poplating multiple cells with same data using VLOOKUP

Copper Contributor

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)

IDNameBirthday
1234John Smith11/24/1998
4321Jane Doe

01/04/1995

 

Destination Sheet 2

IDName

Birthday

(Need BD repeated in this column from sheet 1)

1234John Smith 
1234John Smith 
1234John Smith 
4321Jane Doe 
4321Jane Doe 
3 Replies

@frankp755 

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

vlookup.JPG

sheet1 ID Name Birthday.JPG 

@OliverScheurich Thank you so much. Are you able to explain this to me? I gave a simplified example and have more columns than what I showed as an example. Thank you.

@frankp755 

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

vlookup.JPG

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.