Repeat rows for each value from vlookup from another sheet in Excel

0
votes

I apologize if the title doesn't make any sense since I am trying to figure out how to explain it best.

My question is, is there any formula or Excel magic to make below possible in Excel?

I have Sheet1 in Excel as follows: enter image description here

In another sheet (Sheet2) in the same workbook, I want to populate rows of cities based on each name. I can use data validation to select John Doe or any name. So, if I select "John Doe", I want to find all the cities for John Doe from Sheet 1 and add them to the rows as shown below: enter image description here

1
excelexcel-formula
Pivot table maybe? – BigBen
Or COUNTIFS().. – Scott Craner
@ScottCraner - OP wants to populate the city names themselves so wouldn't work (that's what I thought the point of the question was originally too). – BigBen
Well, Then UNIQUE(FILTER()) – Scott Craner

1 Answers

1
votes

There are several possibilities to do that, depending on what you want to achieve and what Excel version you use.

You can use a PivotTable with the name as the filter for the pivot table.

In Excel 365 you can use the new Filter function.

=FILTER(Sheet1!B:B,Sheet1!A:A=Sheet2!B3)

enter image description here

If the data may have duplicates for the name/city combination, wrap the formula in a Unique() function.

This gets you the desired list. If the other columns are using formula to pull counts and sums from the data set, then that approach will work.

If, however, you want somebody the manually enter data into the grid for "how many", "how long" etc, then formulas or pivot tables are not a good approach.