New Contributor

Poplating multiple cells with same data using VLOOKUP

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?

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
3 Replies

Re: Poplating multiple cells with same data using VLOOKUP

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

Re: Poplating multiple cells with same data using VLOOKUP

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

Re: Poplating multiple cells with same data using VLOOKUP

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