software

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...

thanksAssumptions:

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)gt;0,0))

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

Hope this helps!

In article .comgt;,
quot;mjquot; gt; wrote:

gt; hello guys,
gt;
gt; i just want to know what function will i use to return a header row as
gt; a result. I have a worksheet consisting of lists of images arranged in
gt; columns by subfolder; the name of the subfolder serves as my header.
gt; Now, i have another list of images and i want to crosscheck this list
gt; to my worksheet to know where these images are located or in what
gt; subfolder/s are they included.
gt;
gt; my table looks like this:
gt;
gt; 1 Sub1 Sub2 ... Sub20 (header row)
gt; 2 img1 img101 img201
gt; . . .
gt; . . .
gt; img100 img200 img300
gt;
gt; what i want is: if i search for img150...the result would be
gt; Sub2(header row)
gt;
gt; please help me...
gt;
gt; thanks

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
quot;sub3quot; instead of quot;sub2quot;.i wonder what seems to be the problem?

sub1sub2sub3
11582
31466
51978
10015010
12113611
1301479
561221
143526
193628

{=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,CO LUMN(A2:C10)-COLUMN(A2),1),E2)gt;0,0))}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 .comgt;,
quot;mjquot; gt; wrote:

gt; sir,
gt;
gt; i tried to make a dummy table to try the formula you gave me, however,
gt; it returns the wrong header. when i search for 150, it returns header
gt; quot;sub3quot; instead of quot;sub2quot;.i wonder what seems to be the problem?
gt;
gt; sub1sub2sub3
gt; 11582
gt; 31466
gt; 51978
gt; 10015010
gt; 12113611
gt; 1301479
gt; 561221
gt; 143526
gt; 193628
gt;
gt; {=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,CO LUMN(A2:C10)-COLUMN(A2),1),E
gt; 2)gt;0,0))}

Does not work either. I wonder why!

--
AP

quot;Domenicquot; gt; a écrit dans le message de
...
gt; There's a comma missing in your formula. This part of the formula...
gt;
gt; OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),1)
gt;
gt; should be
gt;
gt; OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),,1)
gt;
gt; Hope this helps!
gt;
gt; In article .comgt;,
gt; quot;mjquot; gt; wrote:
gt;
gt; gt; sir,
gt; gt;
gt; gt; i tried to make a dummy table to try the formula you gave me, however,
gt; gt; it returns the wrong header. when i search for 150, it returns header
gt; gt; quot;sub3quot; instead of quot;sub2quot;.i wonder what seems to be the problem?
gt; gt;
gt; gt; sub1 sub2 sub3
gt; gt; 1 158 2
gt; gt; 3 146 6
gt; gt; 5 197 8
gt; gt; 100 150 10
gt; gt; 121 136 11
gt; gt; 130 147 9
gt; gt; 56 12 21
gt; gt; 14 35 26
gt; gt; 19 36 28
gt; gt;
gt; gt;
{=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,CO LUMN(A2:C10)-COLUMN(A2),1)
,E
gt; gt; 2)gt;0,0))}
In article gt;,
quot;Ardus Petusquot; gt; wrote:

gt; 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.

Incorrect result.
Here is my sample sheet:

cjoint.com/?desg77Wwr1

HTH
--
AP
quot;Domenicquot; gt; a écrit dans le message de
...
gt; In article gt;,
gt; quot;Ardus Petusquot; gt; wrote:
gt;
gt; gt; Does not work either. I wonder why!
gt;
gt; Are you getting an incorrect result? Or are you getting an error value?
gt; Note that the formula needs to be confirmed with CONTROL SHIFT ENTER,
gt; not just ENTER.
gt;
gt; In other words, enter the formula but instead of pressing just ENTER
gt; hold the CONTROL and SHIFT keys down, then while they're pressed down,
gt; press the ENTER key. Excel will automatically place braces {} around
gt; the formula indicating that you've entered the formula correctly.
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. A211.

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

=INDEX(A11,MATCH(1,COUNTIF(OFFSET(A210,,COLUMN (A)-COLUMN(A:A),9,1),
$F$2),0))

But, personally, I prefer the following...

=INDEX(A11,MATCH(TRUE,COUNTIF(OFFSET(A210,,COL UMN(A210)-COLUMN(A2),
,1),$F$2)gt;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.
A210 ---gt; 9.

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

Hope this helps!

In article gt;,
quot;Ardus Petusquot; gt; wrote:

gt; Incorrect result.
gt; Here is my sample sheet:
gt;
gt; cjoint.com/?desg77Wwr1
gt;
gt; HTH
gt; --
gt; AP
gt; quot;Domenicquot; gt; a écrit dans le message de
gt; ...
gt; gt; In article gt;,
gt; gt; quot;Ardus Petusquot; gt; wrote:
gt; gt;
gt; gt; gt; Does not work either. I wonder why!
gt; gt;
gt; gt; Are you getting an incorrect result? Or are you getting an error value?
gt; gt; Note that the formula needs to be confirmed with CONTROL SHIFT ENTER,
gt; gt; not just ENTER.
gt; gt;
gt; gt; In other words, enter the formula but instead of pressing just ENTER
gt; gt; hold the CONTROL and SHIFT keys down, then while they're pressed down,
gt; gt; press the ENTER key. Excel will automatically place braces {} around
gt; gt; the formula indicating that you've entered the formula correctly.

Still does not work!

--
AP

quot;Domenicquot; gt; a écrit dans le message de
...
gt; The 'match type' or third argument for the MATCH function is missing
gt; and, as a result, defaults to 1. You need to set it to 0 for an exact
gt; match.
gt;
gt; Also, the 'height' specified for the OFFSET function will add a row to
gt; your reference/range, i.e. A211.
gt;
gt; So, your formula should be amended as follows...
gt;
gt; =INDEX(A11,MATCH(1,COUNTIF(OFFSET(A210,,COLUMN (A)-COLUMN(A:A),9,1),
gt; $F$2),0))
gt;
gt; But, personally, I prefer the following...
gt;
gt; =INDEX(A11,MATCH(TRUE,COUNTIF(OFFSET(A210,,COL UMN(A210)-COLUMN(A2),
gt; ,1),$F$2)gt;0,0))
gt;
gt; Two reasons:
gt;
gt; 1) If by mistake, there's a duplicate entry for one of the columns, the
gt; formula will still return a correct result.
gt;
gt; 2) By omitting the '9' and leaving the 'height' for the OFFSET function
gt; empty, it automatically defaults to the height of the reference, i.e.
gt; A210 ---gt; 9.
gt;
gt; 3) Personally, I think it looks better. But to each his/her own...
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Ardus Petusquot; gt; wrote:
gt;
gt; gt; Incorrect result.
gt; gt; Here is my sample sheet:
gt; gt;
gt; gt; cjoint.com/?desg77Wwr1
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt; quot;Domenicquot; gt; a écrit dans le message de
gt; gt; ...
gt; gt; gt; In article gt;,
gt; gt; gt; quot;Ardus Petusquot; gt; wrote:
gt; gt; gt;
gt; gt; gt; gt; Does not work either. I wonder why!
gt; gt; gt;
gt; gt; gt; Are you getting an incorrect result? Or are you getting an error
value?
gt; gt; gt; Note that the formula needs to be confirmed with CONTROL SHIFT ENTER,
gt; gt; gt; not just ENTER.
gt; gt; gt;
gt; gt; gt; In other words, enter the formula but instead of pressing just ENTER
gt; gt; gt; hold the CONTROL and SHIFT keys down, then while they're pressed down,
gt; gt; gt; press the ENTER key. Excel will automatically place braces {} around
gt; gt; gt; the formula indicating that you've entered the formula correctly.
Works for me. (using your sample file)

Biff

quot;Ardus Petusquot; gt; wrote in message
...
gt; Still does not work!
gt;
gt; --
gt; AP
gt;
gt; quot;Domenicquot; gt; a écrit dans le message de
gt; ...
gt;gt; The 'match type' or third argument for the MATCH function is missing
gt;gt; and, as a result, defaults to 1. You need to set it to 0 for an exact
gt;gt; match.
gt;gt;
gt;gt; Also, the 'height' specified for the OFFSET function will add a row to
gt;gt; your reference/range, i.e. A211.
gt;gt;
gt;gt; So, your formula should be amended as follows...
gt;gt;
gt;gt; =INDEX(A11,MATCH(1,COUNTIF(OFFSET(A210,,COLUMN (A)-COLUMN(A:A),9,1),
gt;gt; $F$2),0))
gt;gt;
gt;gt; But, personally, I prefer the following...
gt;gt;
gt;gt; =INDEX(A11,MATCH(TRUE,COUNTIF(OFFSET(A210,,COL UMN(A210)-COLUMN(A2),
gt;gt; ,1),$F$2)gt;0,0))
gt;gt;
gt;gt; Two reasons:
gt;gt;
gt;gt; 1) If by mistake, there's a duplicate entry for one of the columns, the
gt;gt; formula will still return a correct result.
gt;gt;
gt;gt; 2) By omitting the '9' and leaving the 'height' for the OFFSET function
gt;gt; empty, it automatically defaults to the height of the reference, i.e.
gt;gt; A210 ---gt; 9.
gt;gt;
gt;gt; 3) Personally, I think it looks better. But to each his/her own...
gt;gt;
gt;gt; Hope this helps!
gt;gt;
gt;gt; In article gt;,
gt;gt; quot;Ardus Petusquot; gt; wrote:
gt;gt;
gt;gt; gt; Incorrect result.
gt;gt; gt; Here is my sample sheet:
gt;gt; gt;
gt;gt; gt; cjoint.com/?desg77Wwr1
gt;gt; gt;
gt;gt; gt; HTH
gt;gt; gt; --
gt;gt; gt; AP
gt;gt; gt; quot;Domenicquot; gt; a écrit dans le message de
gt;gt; gt; ...
gt;gt; gt; gt; In article gt;,
gt;gt; gt; gt; quot;Ardus Petusquot; gt; wrote:
gt;gt; gt; gt;
gt;gt; gt; gt; gt; Does not work either. I wonder why!
gt;gt; gt; gt;
gt;gt; gt; gt; Are you getting an incorrect result? Or are you getting an error
gt; value?
gt;gt; gt; gt; Note that the formula needs to be confirmed with CONTROL SHIFT ENTER,
gt;gt; gt; gt; not just ENTER.
gt;gt; gt; gt;
gt;gt; gt; gt; In other words, enter the formula but instead of pressing just ENTER
gt;gt; gt; gt; hold the CONTROL and SHIFT keys down, then while they're pressed
gt;gt; gt; gt; down,
gt;gt; gt; gt; press the ENTER key. Excel will automatically place braces {} around
gt;gt; gt; gt; the formula indicating that you've entered the formula correctly.
gt;
gt;