Results 1 to 7 of 7
Thread: Array Formula  IF (2002)

20060517, 19:21 #1
 Join Date
 Mar 2004
 Location
 North Carolina, USA
 Posts
 268
 Thanks
 0
 Thanked 0 Times in 0 Posts
Array Formula  IF (2002)
This array formula only works on the first value in a list (A26) and returns a N/A for all others. I am doing cntrl+shift+enter prior to entering. Can someone advise on what I am doing wrong???
Thanks.
=IF(A26=$D$5:$D$19, D26/F5, "N/A")

20060517, 19:27 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Array Formula  IF (2002)
You only provide A26, not a list of values, so the result is to be expected. Can you explain what you want to accomplish? Please try to be clear and exact.

20060517, 19:43 #3
 Join Date
 Mar 2004
 Location
 North Carolina, USA
 Posts
 268
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Array Formula  IF (2002)
Hans, I want the formula to look in A26 for this value and if there is a match in the list d5 thru d19, then I want the division to take place between the adjacent columns. For example, if there is a match in d5 then I want to do the following division in the cell I have this formula d26/f5.
List one is comprised of: A2655
List two is comprised of: d4:f19
Does this make sense?

20060517, 19:58 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Array Formula  IF (2002)
Not really, I'm afraid. I'll take a guess: try the following standard (nonarray) formula:
=D26/VLOOKUP(A26,$D$5:$F$19,3,FALSE)
and fill down as far as needed.

20060517, 20:35 #5
 Join Date
 Mar 2004
 Location
 North Carolina, USA
 Posts
 268
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Array Formula  IF (2002)
Hans, thanks. This gives me the desired result. Was this also possible w/an array.
I am trying to better understand arrays...

20060517, 20:49 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Array Formula  IF (2002)
Not with VLOOKUP, but you could use INDEX and MATCH:
 Select A26:A55.
 Enter the formula
<code>
=D2655/INDEX(F5:F19,MATCH(A26:A55,D519,0))
</code>
 Confirm with Ctrl+Shift+Enter to make it an array formula.

20060518, 10:45 #7
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: Array Formula  IF (2002)
We use quite a few array formula setups in place of pivot table type layouts.
try this, where column B3 down contains the alphabet, column C3 down contains values, D3 is my lookup letter, E3 is my array formula cell, F3 is my divisor
<pre>{=SUM(IF(D3=$B$3:$B$14,$C$3:$C$14/$F$3))}</pre>
Alan