How to use a lookup, hlookup

**kryt0n** · 06-14-2010, 10:34 AM

OK, so I have a number of coloums with figures.

What I want to say is:
If the column is not 0 but is a number (1,2,3...) then look to the column heading and display that heading.

Is that possible? Do I need a lookup for that one?

**NBVC** · 06-14-2010, 10:41 AM

As I had asked previously, we need to see a sample setup to give an accurate reply... this can be interpreted in a variety of ways.

**kryt0n** · 06-14-2010, 10:51 AM

Sorry, it kept crashing when I tried to edit the last one.

Ok, let me try again:

heading1 heading2 heading3 heading4 heading 5
0 0 0 5 0
0 3 4 6 0
4 2 2 0 0

What I need is to say that:
reading across the columns, when you hit a number, display that header in a cell, then when you reach 0 again, display that heading.

So it would go for the examples above:

cell1 cell2
heading4 heading5
heading2 heading5
heading1 heading4

Hope that explains things better.

**NBVC** · 06-14-2010, 11:00 AM

Assuming your table is in A1:E4, then try:

=INDEX($A$1:$E$1,MATCH(TRUE,INDEX(A2:E2>0,0),0))

copied down to get where first non-zero item occurs per row.

and

=INDEX(INDEX($A$1:$E$1,MATCH(A9,$A$1:$E$1,0)):$E$1,MATCH(0,INDEX(A2:E2,MATCH(A9,$A$1:$E$1,0)):E2,0))

where first 0 after occurs... copied down.