Index and Match checking multiple columns

[kcochran12]

Using Excel 2007

Hello,

I am trying to return the amount from table 2 to table 1. I've used the following formula, =index(A2:A9,match(a2,I2:I9,0)), in B2 of table 1. I've copied down the formula but once the account that I need is in a different column I get the #N/A error. I need the formula to then check the other lookup columns in table 2 for the account numbers and return the amount that is in its respective row. Please help.

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2345
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3456
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4567
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5678
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6789
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7890
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8901
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9012
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Amount
[/TD]
[TD]Data 1
[/TD]
[TD]Data 2
[/TD]
[TD]Data 3
[/TD]
[TD]Data 4
[/TD]
[TD]lookup 1
[/TD]
[TD]lookup 2
[/TD]
[TD]lookup 3
[/TD]
[TD]lookup 4
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1234
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2345
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3456
[/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4567
[/TD]
[/TR]
[TR]
[TD]50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5678
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6789
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7890
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8901
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9012
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Welcome to the forum.
This solution assumes that each account appears only once in the columns Lookup1...4.

Book1
	A	B	C	D	E	F	G	H	I
1	Table 1
2	Account	Amount
3	1234	10
4	2345	20
5	3456	30
6	4567	40
7	5678	50
8	6789	60
9	7890	70
10	8901	80
11	9012	90
12
13
14	Table 2
15	Amount	Data 1	Data 2	Data 3	Data 4	lookup 1	lookup 2	lookup 3	lookup 4
16	10								1234
17	20								2345
18	30								3456
19	40								4567
20	50							5678
21	60							6789
22	70						7890
23	80					8901
24	90					9012
Sheet53

Cell Formulas
Range	Formula
B3	=SUMPRODUCT((A3=$F$16:$I$24)*$A$16:$A$24)

 

[kcochran12]

DRSteele, thank you for the quick response. I still get #N/A using your formula but i'm hoping it's because of an error i made on my initial post. I'm using excel 2010 not 2007. Apologies.

 

[kcochran12]

I should also mention that the data in the lookup columns are the results of a vlookup formula. Don't know if that will/should make a difference.

thanks again