Matching Formula

[Tandy]

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

 

[Luke M]

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

 

[Francis]

-- 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]

Hi Francis-

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

Thanks,

Tandy

 

[Tandy]

Hi Luke-

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

Thank you so much,

Tandy

 

[Luke M]

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.

 

[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

 

[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]

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.