In the formula, make sure the arrays within SUMPRODUCT do not callout entire
columns (B:B) and the headers or any other text are not included. Arrays
sizes within SUMPRODUCT must also be equal in size.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tandy" wrote:

Hi Luke-

I just converted all of my account numbers to numbers but the formula still
does not work. And I do have gaps in my ranges.

Tandy

"Luke M" wrote:

Are your account numbers truly entered as numbers, or are they text?

Also, as Francis suggested, you might be able to use a VLOOKUP formula if
there are no gaps in your ranges. All you need to do is change the table
reference.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tandy" wrote:

Hi Luke-

This formula came back with an error (#VALUE). Do you have any ideas??

Thank you so much,

Tandy

"Luke M" wrote:

Assuming Table 2 is on Sheet2
=INDEX(Sheet2!C:C,SUMPRODUCT(--(Sheet2!A$2:A$100<=A2),--(Sheet2!A$2:A$100=A2),ROW(A$2:A$100)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tandy" wrote:

Hi Everyone-

I need some help with a formula. Basically I have two tables.

Table 1
A B
1 Account Node
2 1001 (blank)

Table 2
A B C
1 Acct From Acct To Node
2 1000 2000 Cash

What I need to do is fill in the Node field in Table 1 using the information
in Table 2. Normally I would use an INDEX/MATCH formula. However, I do not
know how to tell the formula that it needs to see if the account listed in
Table 1 is between the accounts listed in Table 2 and if it is to take the
Node listed in Table 2 into the Node field in Table 1.

Could anyone help me with this? It would be greatly, greatly appreciated!

Thank you,

Tandy