Lookup table and repeat items corresponding number of times found in table

[mellis123]

Hi,
I am trying to create a lookup that looks at the following table:

Windows Doors Roofs Chimneys
Building 1 2 4 1 4
Building 2 9 5 2 8
Building 3 18 5 1 4


I want to input into a cell BUILDING 1

And return the following:

Window
Window
Door
Door
Door
Door
Roof
Chimney
Chimney
Chimney
Chimney


Example attached.
Any help would be much appreciated!

[NBVC]

Try this:

Add a helper row... so in B7 enter:

=SUM(INDEX($B3:$B5,MATCH($A$10,$A$3:$A$5,0)):INDEX(B3:B5,MATCH($A$10,$A$3:$A$5,0)))

copied across the 4 columns. This gives cumulative sum of specific building product requirements.. now in B11:

=IF(ROWS($A$11:$A11)>$E$7,"",INDEX($B$2:$E$2,MIN(IF(ROWS(A$11:A11)<=$B$7:$E$7,COLUMN($B$2:$E$2)-COLUMN($B$2)+1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down as far as you want in order to ensure you always capture all possibilities.

if you don't want the ending "s" change the table titles at top.

Please Login or Register  to view this content.

[mellis123]

Thats great, thankyou very much.

[NBVC]

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save