You could use a VLOOKUP, maybe something like...

Let's say your data range is A5:E1000

In cell B2 type =VLOOKUP(A2,A5:E1000,2)

If you type "Chair" into A2 the result in B2 will be 1 as the formula
returns the value from Column 2 in the same row as "Chair". Repeat the
formula in B3,B4 and B5 but replace the 2 at the end of the formula with 3
for B3, 4 for B4 and 5 for B5 and it should return all the room numbers.

Hope this helps,

Gav.

Repeat this formula for

"raphiel2063" wrote:

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?