returning header row as a result...
 

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

returning header row as a result...
 

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,,COL UMN(A2:C10)-COLUMN(A2),
,1),E2)0,0))

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

Hope this helps!

In article .com,
"mj" wrote:

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

returning header row as a result...
 

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,,CO LUMN(A2:C10)-COLUMN(A2),1),E2)0,0))}

returning header row as a result...
 

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!

In article .com,
"mj" wrote:

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,,CO LUMN(A2:C10)-COLUMN(A2),1),E
2)0,0))}

returning header row as a result...
 

Does not work either. I wonder why!

--
AP

"Domenic" a écrit dans le message de
...
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!

In article .com,
"mj" wrote:

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,,CO LUMN(A2:C10)-COLUMN(A2),1)
,E
2)0,0))}

returning header row as a result...
 

In article ,
"Ardus Petus" wrote:

Does not work either. I wonder why!

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.

returning header row as a result...
 

Incorrect result.
Here is my sample sheet:

http://cjoint.com/?desg77Wwr1

HTH
--
AP
"Domenic" a écrit dans le message de
...
In article ,
"Ardus Petus" wrote:

Does not work either. I wonder why!

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.

returning header row as a result...
 

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,,COL UMN(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!

In article ,
"Ardus Petus" wrote:

Incorrect result.
Here is my sample sheet:

http://cjoint.com/?desg77Wwr1

HTH
--
AP
"Domenic" a écrit dans le message de
...
In article ,
"Ardus Petus" wrote:

Does not work either. I wonder why!

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.

returning header row as a result...
 

Still does not work!

--
AP

"Domenic" a écrit dans le message de
...
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,,COL UMN(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!

In article ,
"Ardus Petus" wrote:

Incorrect result.
Here is my sample sheet:

http://cjoint.com/?desg77Wwr1

HTH
--
AP
"Domenic" a écrit dans le message de
...
In article ,
"Ardus Petus" wrote:

Does not work either. I wonder why!

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.

returning header row as a result...
 

Works for me. (using your sample file)

Biff

"Ardus Petus" wrote in message
...
Still does not work!

--
AP

"Domenic" a écrit dans le message de
...
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,,COL UMN(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!

In article ,
"Ardus Petus" wrote:

Incorrect result.
Here is my sample sheet:

http://cjoint.com/?desg77Wwr1

HTH
--
AP
"Domenic" a écrit dans le message de
...
In article ,
"Ardus Petus" wrote:

Does not work either. I wonder why!

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.

returning header row as a result...
 

In article ,
"Ardus Petus" wrote:

Still does not work!

Would you like me to send you a sample file?

returning header row as a result...
 

Here is my file: http://cjoint.com/?devmAKL3mn

--
AP

"Domenic" a écrit dans le message de
...
In article ,
"Ardus Petus" wrote:

Still does not work!

Would you like me to send you a sample file?

returning header row as a result...
 

Sorry I misunderstood: yes, please send me a sample file.

--
AP

"Domenic" a écrit dans le message de
...
In article ,
"Ardus Petus" wrote:

Still does not work!

Would you like me to send you a sample file?

returning header row as a result...
 

It returns "sub4" for all items in col sub1

Windows 2000 + Office 2000

HTH
--
AP

"Domenic" a écrit dans le message de
...
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,,COL UMN(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!

In article ,
"Ardus Petus" wrote:

Incorrect result.
Here is my sample sheet:

http://cjoint.com/?desg77Wwr1

HTH
--
AP
"Domenic" a écrit dans le message de
...
In article ,
"Ardus Petus" wrote:

Does not work either. I wonder why!

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.

returning header row as a result...
 

Sample file sent...

In article ,
"Ardus Petus" wrote:

Sorry I misunderstood: yes, please send me a sample file.

--
AP

returning header row as a result...
 

Thanks, it works!

Sorry for the annoyance...

--
AP

"Domenic" a écrit dans le message de
...
Sample file sent...

In article ,
"Ardus Petus" wrote:

Sorry I misunderstood: yes, please send me a sample file.

--
AP

returning header row as a result...
 

In article ,
"Ardus Petus" wrote:

Thanks, it works!

You're very welcome! Glad I could help!

Sorry for the annoyance...

No problem at all... :)

Cheers!

returning header row as a result...
 

Thanks a lot!!! it's working...

mj