# Thread: Array Formula - IF (2002)

1. ## 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")

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

3. ## 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?

4. ## Re: Array Formula - IF (2002)

Not really, I'm afraid. I'll take a guess: try the following standard (non-array) formula:

=D26/VLOOKUP(A26,\$D\$5:\$F\$19,3,FALSE)

and fill down as far as needed.

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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•