Formula Help

[serge]

Hi everyone,

I need some help this formula :

=INDEX(X6:AG6,1,K6)

When in K6 I have a " 0 " this formula give me back : # VALUE!
Can someone help me Please for the formula to recognize the " 0 ".

Thank you all in advance.

Serge.

 

[Aladin Akyurek]

Hi everyone,

I need some help this formula :

=INDEX(X6:AG6,1,K6)

When in K6 I have a " 0 " this formula give me back : # VALUE!
Can someone help me Please for the formula to recognize the " 0 ".

Thank you all in advance.

Serge.

When K6 is set to 0, INDEX is asked to pick out all of the values from X6:AG6. That's, the result is an array, not a single value. Hence #VALUE!...
But, it can be fed to other functions like in:

=COUNT(INDEX(X6:AG6,1,K6))

=COUNTA(INDEX(X6:AG6,1,K6))

=SUM(INDEX(X6:AG6,1,K6))

would all work.

Note that:

=INDEX(X6:AG6,1,1)

=INDEX(X6:AG6,1,K6)

where K6 = 1, would yield a single value.

 

[serge]

Hi Aladin,

Thank you, for your quick respond but I tried all your formulas but none works for what I need !!!
Here I made an example :
The columns C,D,E,F,G,H,I,J,K,L calculate the skip of the digit in column A with the following formula :

C3 : =IF($A2=0,1,C2+1)
D3 : =IF($A2=1,1,D2+1)
E3 : =IF($A2=2,1,E2+1)
F3 : =IF($A2=3,1,F2+1)
G3 : =IF($A2=4,1,G2+1)
H3 : =IF($A2=5,1,H2+1)
I3 : =IF($A2=6,1,I2+1)
J3 : =IF($A2=7,1,J2+1)
K3 : =IF($A2=8,1,K2+1)
L3 : =IF($A2=9,1,L2+1)

And in column N the formula should report the number corresponding from the columns C : L.
See example below :

A B C D E F G H I J K L M N

<table width="440" border="0" cellpadding="0" cellspacing="0"><col style="width: 20pt;" width="26" span="12"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 20pt;" width="26" height="17">
</td> <td style="width: 20pt;" width="26">
</td> <td class="xl73" style="width: 20pt;" width="26">0</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">1</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">2</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">3</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">4</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">5</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">6</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">7</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">8</td> <td class="xl73" style="border-left: medium none; width: 20pt;" width="26">9</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl69">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt;" height="17">1</td> <td class="xl70">
</td> <td class="xl71">1</td> <td class="xl71">1</td> <td class="xl71">1</td> <td class="xl71">1</td> <td class="xl71">1</td> <td class="xl71">1</td> <td class="xl71">1</td> <td class="xl71">1</td> <td class="xl71">1</td> <td class="xl71">1</td> <td>
</td> <td class="xl74">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">0</td> <td class="xl70">
</td> <td class="xl71">2</td> <td class="xl71">1</td> <td class="xl71">2</td> <td class="xl71">2</td> <td class="xl71">2</td> <td class="xl71">2</td> <td class="xl71">2</td> <td class="xl71">2</td> <td class="xl71">2</td> <td class="xl71">2</td> <td>
</td> <td class="xl74" style="border-top: medium none;">#VALUE!
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">0</td> <td class="xl70">
</td> <td class="xl71">1</td> <td class="xl71">2</td> <td class="xl71">3</td> <td class="xl71">3</td> <td class="xl71">3</td> <td class="xl71">3</td> <td class="xl71">3</td> <td class="xl71">3</td> <td class="xl71">3</td> <td class="xl71">3</td> <td>
</td> <td class="xl74" style="border-top: medium none;">#VALUE!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">0</td> <td class="xl70">
</td> <td class="xl71">1</td> <td class="xl71">3</td> <td class="xl71">4</td> <td class="xl71">4</td> <td class="xl71">4</td> <td class="xl71">4</td> <td class="xl71">4</td> <td class="xl71">4</td> <td class="xl71">4</td> <td class="xl71">4</td> <td>
</td> <td class="xl74" style="border-top: medium none;">#VALUE!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">0</td> <td class="xl70">
</td> <td class="xl71">1</td> <td class="xl71">4</td> <td class="xl71">5</td> <td class="xl71">5</td> <td class="xl71">5</td> <td class="xl71">5</td> <td class="xl71">5</td> <td class="xl71">5</td> <td class="xl71">5</td> <td class="xl71">5</td> <td>
</td> <td class="xl74" style="border-top: medium none;">#VALUE!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">7</td> <td class="xl70">
</td> <td class="xl71">1</td> <td class="xl71">5</td> <td class="xl71">6</td> <td class="xl71">6</td> <td class="xl71">6</td> <td class="xl71">6</td> <td class="xl71">6</td> <td class="xl71">6</td> <td class="xl71">6</td> <td class="xl71">6</td> <td>
</td> <td class="xl74" style="border-top: medium none;">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">3</td> <td class="xl70">
</td> <td class="xl71">2</td> <td class="xl71">6</td> <td class="xl71">7</td> <td class="xl71">7</td> <td class="xl71">7</td> <td class="xl71">7</td> <td class="xl71">7</td> <td class="xl71">1</td> <td class="xl71">7</td> <td class="xl71">7</td> <td>
</td> <td class="xl74" style="border-top: medium none;">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">0</td> <td class="xl70">
</td> <td class="xl71">3</td> <td class="xl71">7</td> <td class="xl71">8</td> <td class="xl71">1</td> <td class="xl71">8</td> <td class="xl71">8</td> <td class="xl71">8</td> <td class="xl71">2</td> <td class="xl71">8</td> <td class="xl71">8</td> <td>
</td> <td class="xl74" style="border-top: medium none;">#VALUE!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">0</td> <td class="xl70">
</td> <td class="xl71">1</td> <td class="xl71">8</td> <td class="xl71">9</td> <td class="xl71">2</td> <td class="xl71">9</td> <td class="xl71">9</td> <td class="xl71">9</td> <td class="xl71">3</td> <td class="xl71">9</td> <td class="xl71">9</td> <td>
</td> <td class="xl74" style="border-top: medium none;">#VALUE!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">3</td> <td class="xl70">
</td> <td class="xl71">1</td> <td class="xl71">9</td> <td class="xl71">10</td> <td class="xl71">3</td> <td class="xl71">10</td> <td class="xl71">10</td> <td class="xl71">10</td> <td class="xl71">4</td> <td class="xl71">10</td> <td class="xl71">10</td> <td>
</td> <td class="xl74" style="border-top: medium none;">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">0</td> <td class="xl70">
</td> <td class="xl71">2</td> <td class="xl71">10</td> <td class="xl71">11</td> <td class="xl71">1</td> <td class="xl71">11</td> <td class="xl71">11</td> <td class="xl71">11</td> <td class="xl71">5</td> <td class="xl71">11</td> <td class="xl71">11</td> <td>
</td> <td class="xl74" style="border-top: medium none;">#VALUE!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">3</td> <td class="xl70">
</td> <td class="xl71">1</td> <td class="xl71">11</td> <td class="xl71">12</td> <td class="xl71">2</td> <td class="xl71">12</td> <td class="xl71">12</td> <td class="xl71">12</td> <td class="xl71">6</td> <td class="xl71">12</td> <td class="xl71">12</td> <td>
</td> <td class="xl74" style="border-top: medium none;">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">2</td> <td class="xl70">
</td> <td class="xl71">2</td> <td class="xl71">12</td> <td class="xl71">13</td> <td class="xl71">1</td> <td class="xl71">13</td> <td class="xl71">13</td> <td class="xl71">13</td> <td class="xl71">7</td> <td class="xl71">13</td> <td class="xl71">13</td> <td>
</td> <td class="xl74" style="border-top: medium none;">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">7</td> <td class="xl70">
</td> <td class="xl71">3</td> <td class="xl71">13</td> <td class="xl71">1</td> <td class="xl71">2</td> <td class="xl71">14</td> <td class="xl71">14</td> <td class="xl71">14</td> <td class="xl71">8</td> <td class="xl71">14</td> <td class="xl71">14</td> <td>
</td> <td class="xl74" style="border-top: medium none;">14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none;" height="17">2</td> <td class="xl70">
</td> <td class="xl71">4</td> <td class="xl71">14</td> <td class="xl71">2</td> <td class="xl71">3</td> <td class="xl71">15</td> <td class="xl71">15</td> <td class="xl71">15</td> <td class="xl71">1</td> <td class="xl71">15</td> <td class="xl71">15</td> <td>
</td> <td class="xl74" style="border-top: medium none;">14
</td> </tr> </tbody></table>
But all my return value are wrong by 1 column because the " 0 " !!!

In N3 it should be : 1
In N4 it should be : 2
In N5 it should be : 1
In N6 it should be : 1
In N7 it should be : 1
In N8 it should be : 6
In N9 it should be : 7
In N10 it should be : 3
In N11 it should be : 1
In N12 it should be : 3
In N12 it should be : 2
In N12 it should be : 2
In N12 it should be : 13
In N12 it should be : 8
In N12 it should be : 2

I hope my explanation help ?

Thank you. Serge.

 

[leigao84]

All arrays in EXCEL starts with the INDEX number 1! So column 0 doesn't make any sense since the 0th column doesn't exist (no matter how you name them)!

You might want to add a MATCH statement or simply just do
=INDEX(X6:AG6,1,K6+1)

 

[serge]

Hi Leiga,

Thank you very much for your quick and accurate response, it works perfectly now.