returning header row as a result...

M

mj

hello guys,

i just want to know what function will i use to return a header row as
a result. I have a worksheet consisting of lists of images arranged in
columns by subfolder; the name of the subfolder serves as my header.
Now, i have another list of images and i want to crosscheck this list
to my worksheet to know where these images are located or in what
subfolder/s are they included.

my table looks like this:

1 Sub1 Sub2 ... Sub20 (header row)
2 img1 img101 img201
. . .
. . .
img100 img200 img300

what i want is: if i search for img150...the result would be
Sub2(header row)

please help me...

thanks
 
D

Domenic

Assumptions:

A1:C1 contains your headers

A2:C10 contains your data

E2 contains the image of interest, such as img150

Formula:

=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),
,1),E2)>0,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
M

mj

sir,

i tried to make a dummy table to try the formula you gave me, however,
it returns the wrong header. when i search for 150, it returns header
"sub3" instead of "sub2".i wonder what seems to be the problem?

sub1 sub2 sub3
1 158 2
3 146 6
5 197 8
100 150 10
121 136 11
130 147 9
56 12 21
14 35 26
19 36 28

{=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),1),E2)>0,0))}
 
D

Domenic

There's a comma missing in your formula. This part of the formula...

OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),1)

should be

OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),,1)

Hope this helps!
 
A

Ardus Petus

Does not work either. I wonder why!

--
AP

Domenic said:
There's a comma missing in your formula. This part of the formula...

OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),1)

should be

OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),,1)

Hope this helps!

{=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),1)
,E
Click to expand...
 
D

Domenic

Ardus Petus said:
Does not work either. I wonder why!
Click to expand...

Are you getting an incorrect result? Or are you getting an error value?
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

In other words, enter the formula but instead of pressing just ENTER
hold the CONTROL and SHIFT keys down, then while they're pressed down,
press the ENTER key. Excel will automatically place braces {} around
the formula indicating that you've entered the formula correctly.
 
D

Domenic

The 'match type' or third argument for the MATCH function is missing
and, as a result, defaults to 1. You need to set it to 0 for an exact
match.

Also, the 'height' specified for the OFFSET function will add a row to
your reference/range, i.e. A2:D11.

So, your formula should be amended as follows...

=INDEX(A1:D1,MATCH(1,COUNTIF(OFFSET(A2:D10,,COLUMN(A:D)-COLUMN(A:A),9,1),
$F$2),0))

But, personally, I prefer the following...

=INDEX(A1:D1,MATCH(TRUE,COUNTIF(OFFSET(A2:D10,,COLUMN(A2:D10)-COLUMN(A2),
,1),$F$2)>0,0))

Two reasons:

1) If by mistake, there's a duplicate entry for one of the columns, the
formula will still return a correct result.

2) By omitting the '9' and leaving the 'height' for the OFFSET function
empty, it automatically defaults to the height of the reference, i.e.
A2:D10 ---> 9.

3) Personally, I think it looks better. But to each his/her own... :)

Hope this helps!
 
A

Ardus Petus

Still does not work!

--
AP

Domenic said:
The 'match type' or third argument for the MATCH function is missing
and, as a result, defaults to 1. You need to set it to 0 for an exact
match.

Also, the 'height' specified for the OFFSET function will add a row to
your reference/range, i.e. A2:D11.

So, your formula should be amended as follows...

=INDEX(A1:D1,MATCH(1,COUNTIF(OFFSET(A2:D10,,COLUMN(A:D)-COLUMN(A:A),9,1),
$F$2),0))

But, personally, I prefer the following...

=INDEX(A1:D1,MATCH(TRUE,COUNTIF(OFFSET(A2:D10,,COLUMN(A2:D10)-COLUMN(A2),
,1),$F$2)>0,0))

Two reasons:

1) If by mistake, there's a duplicate entry for one of the columns, the
formula will still return a correct result.

2) By omitting the '9' and leaving the 'height' for the OFFSET function
empty, it automatically defaults to the height of the reference, i.e.
A2:D10 ---> 9.

3) Personally, I think it looks better. But to each his/her own... :)

Hope this helps!
Click to expand...
 
Top