Looling up the value using multiple columns

[jaya0785]

I have two tables.

<table style="border-collapse: collapse; width: 108pt;" width="144" border="0" cellpadding="0" cellspacing="0"><col style="width: 35pt;" width="47"> <col style="width: 40pt;" width="53"> <col style="width: 33pt;" width="44"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 35pt;" width="47" height="20">ID</td> <td class="xl65" style="width: 40pt;" width="53">Subject</td> <td class="xl65" style="width: 33pt;" width="44">Marks</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1</td> <td class="xl65">aaa</td> <td class="xl65">34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1</td> <td class="xl65">bbb</td> <td class="xl65">78</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2</td> <td class="xl65">aaa</td> <td class="xl65">74</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3</td> <td class="xl65">bbb</td> <td class="xl65">76</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4</td> <td class="xl65">ccc</td> <td class="xl65">54</td> </tr> </tbody></table>

<table style="border-collapse: collapse; width: 144pt;" width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">ID</td> <td class="xl65" style="width: 48pt;" width="64">Subject</td> <td class="xl65" style="width: 48pt;" width="64">Name</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1</td> <td class="xl65">aaa</td> <td class="xl65">AAA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1</td> <td class="xl65">bbb</td> <td class="xl65">AAA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3</td> <td class="xl65">bbb</td> <td class="xl65">CCC</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4</td> <td class="xl65">ccc</td> <td class="xl65">DDD</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4</td> <td class="xl65">ddd</td> <td class="xl65">EEE</td> </tr> </tbody></table>


The output should be

<table style="border-collapse: collapse; width: 192pt;" width="256" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">ID</td> <td class="xl65" style="width: 48pt;" width="64">Subject</td> <td class="xl65" style="width: 48pt;" width="64">Name</td> <td class="xl65" style="width: 48pt;" width="64">Marks</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1</td> <td class="xl65">aaa</td> <td class="xl65">AAA</td> <td class="xl65">34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1</td> <td class="xl65">bbb</td> <td class="xl65">AAA</td> <td class="xl65">78</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3</td> <td class="xl65">bbb</td> <td class="xl65">CCC</td> <td class="xl65">76</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4</td> <td class="xl65">ccc</td> <td class="xl65">DDD</td> <td class="xl65">54</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4</td> <td class="xl65">ddd</td> <td class="xl65">EEE</td> <td class="xl65">
</td> </tr> </tbody></table>


This is just a sample. Both the tables have many more fields. My requirement is to find the value from table1 given the two fields. Put them in the table2.

In Table2, I need to fill the Marks value using the table1.
For each row, with the help of two fields ID and subject, i need to lookup the Marks value in table1.

Any help is highly appreciated.

Thanks in advance,
Jaya

 

[sanrv1f]

Welcome to the board,

Try this

Excel Workbook
	A	B	C
1	ID	Subject	Marks
2	1	aaa	34
3	1	bbb	78
4	2	aaa	74
5	3	bbb	76
6	4	ccc	54
Sheet1

Excel 2003

Excel Workbook
	H	I	J	K
1	ID	Subject	Name	Marks
2	1	aaa	AAA	34
3	1	bbb	AAA	78
4	3	bbb	CCC	76
5	4	ccc	DDD	54
6	4	ddd	EEE
Sheet1

Excel 2003

Cell Formulas
Range	Formula
K2	=IF(SUM(INDEX(($A$2:$A$6=H2)*($B$2:$B$6=I2),0)),INDEX($C$2:$C$6,MATCH(1,INDEX(($A$2:$A$6=H2)*($B$2:$B$6=I2),0),0)),"")

 

[jaya0785]

Re: Looking up the value using multiple columns

Thanks a lot. It worked well... It helped me a lot. Thanks again.