Matching Formula

  • Thread starter Thread starter Tandy
  • Start date Start date
T

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
 
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)))
 
-- 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
 
Hi Francis-

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

Thanks,

Tandy
 
Hi Luke-

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

Thank you so much,

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.
 
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
 
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
 
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.
 
Back
Top