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

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

-- try in B2, place the following formula

Assuming Table1 is from A1 and Table 2 is from D1 to F4 on the same sheet

=VLOOKUP(A2,$D$1:$F$4,3,1)
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"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

Hi Francis-

The tables are not on the same sheet. Do you have any other ideas?

Thanks,

Tandy

"Francis" wrote:


-- try in B2, place the following formula

Assuming Table1 is from A1 and Table 2 is from D1 to F4 on the same sheet

=VLOOKUP(A2,$D$1:$F$4,3,1)
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"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

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

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

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

Hi Luke-

I just convented all of my account numbers to numbers and the formula still
doesn't 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

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