I'm trying to set up a sort of filter so that I can pull information from one
main page.

Room 1 Room 2 Room 3 Room 4
Chair 1 2 1
Table 1 2 1
Stool 1

The main data sheet looks (sort of ) like the above with individual products
down the left and their locations across the top. In reality there are
hundreds of products and hundreds of locations.

I want to have a formula/macro which looks up the word 'chair' for example,
then returns me the quantity with the corresponding heading (location), but I
need it to skip the blanks.

I.e. If I ask it to look up 'Chair', the following is displayed

Room 1 Room 2 Room 3
Chair 1 2 1


If I asked it look up 'Stool' it would display

Room 3
Stool 1



Any ideas?