Need Match Formula

[CARBOB]

Need formula for column J, using column D:F, to find the values that match the values in column K, using G:I.

Excel Workbook
	C	D	E	F	G	H	I	J	K
2	07/26/11	9	3	3	C1	C9	B13	0	C3
3	07/25/11	7	9	6	B1	D4	C3	1	B5
4	07/24/11	7	0	7	B4	B1	B7	2	D4
5	07/23/11	0	5	2	B4	B2	C2	5	C4
6	07/22/11	1	6	4	D5	C9	D3	4	D5
7	07/21/11	5	2	9	C2	B3	C1	6	D2
8	07/20/11	7	9	9	B3	D4	D6	7	B2
9	07/19/11	0	5	4	D10	C4	C1	8	D9
10	07/18/11	2	4	8	D3	C6	B3	9	B1
11								3	C1
12								0	B4
Sheet1

Excel 2007

 

[markmzz]

Need formula for column J, using column D:F, to find the values that match the values in column K, using G:I.

Excel Workbook
	C	D	E	F	G	H	I	J	K
2	07/26/11	9	3	3	C1	C9	B13	0	C3
3	07/25/11	7	9	6	B1	D4	C3	1	B5
4	07/24/11	7	0	7	B4	B1	B7	2	D4
5	07/23/11	0	5	2	B4	B2	C2	5	C4
6	07/22/11	1	6	4	D5	C9	D3	4	D5
7	07/21/11	5	2	9	C2	B3	C1	6	D2
8	07/20/11	7	9	9	B3	D4	D6	7	B2
9	07/19/11	0	5	4	D10	C4	C1	8	D9
10	07/18/11	2	4	8	D3	C6	B3	9	B1
11								3	C1
12								0	B4
Sheet1

Excel 2007

CARBOB,

What would be the expected result?

Note: you have repeated values in the region G:I.

Markmzz

 

[CARBOB]

CARBOB,

What would be the expected result?

Note: you have repeated values in the region G:I.

Markmzz

I have some of the expexted results in Col J.

 

[markmzz]

Lets look at B5:

B5 => 1 in columns J:K, but I can't see B5 in the range G:I.

Could you give more informations?

Markmzz

 

[CARBOB]

Lets look at B5:

B5 => 1 in columns J:K, but I can't see B5 in the range G:I.

Could you give more informations?

Markmzz

A different image where the info in the columns match. Using column I as example C3, the value 0 in Col J comes from Col C-3 rows down. The values BX,CX,DX represent the column and the row where value came from. Another example, look at value 9 in H10, comes from B1.

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	07/26/11	9	3	3	C1	C9	B13	0	C3
3	07/25/11	7	9	6	B1	D4	C3	1	B5
4	07/24/11	7	0	7	B4	B1	B7	2	D4
5	07/23/11	0	5	2	B4	B2	C2	5	C4
6	07/22/11	1	6	4	D5	C9	D3	4	D5
7	07/21/11	5	2	9	C2	B3	C1	6	D2
8	07/20/11	7	9	9	B3	D4	D6	7	B2
9	07/19/11	0	5	4	D10	C4	C1	8	D9
10	07/18/11	2	4	8	D3	C6	B3	9	B1
11								3	C1
12								0	B4
Sheet1

Excel 2007

 

[CARBOB]

Tried to get the formula below to work, getting #NA error. The value in H2 should be 0.



=INDEX($B$2:$D$10,MATCH($I2,$E$2:$G$10,0))

Excel Workbook
	A	B	C	D	E	F	G	H	I
1
2	07/26/11	9	3	3	C1	C9	B13	#N/A	C3
3	07/25/11	7	9	6	B1	D4	C3	1	B5
4	07/24/11	7	0	7	B4	B1	B7	2	D4
5	07/23/11	0	5	2	B4	B2	C2	5	C4
6	07/22/11	1	6	4	D5	C9	D3	4	D5
7	07/21/11	5	2	9	C2	B3	C1	6	D2
8	07/20/11	7	9	9	B3	D4	D6	7	B2
9	07/19/11	0	5	4	D10	C4	C1	8	D9
10	07/18/11	2	4	8	D3	C6	B3	9	B1
11								3	C1
12								0	B4
Sheet1

Excel 2007

Cell Formulas
Range	Formula
H2	=INDEX($B$2:$D$10,MATCH($I2,$E$2:$G$10,0))

 

[markmzz]

CARBOB,

Try this:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">7/26/2011</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">C9</td><td style="text-align: center;;">B13</td><td style="text-align: center;;">0</td><td style="text-align: center;background-color: #FFFF00;;">C3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">7/25/2011</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;;">B1</td><td style="text-align: center;;">D4</td><td style="text-align: center;;">C3</td><td style="text-align: center;;">1</td><td style="text-align: center;background-color: #FFFF00;;">B5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">7/24/2011</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;;">B4</td><td style="text-align: center;;">B1</td><td style="text-align: center;;">B7</td><td style="text-align: center;;">2</td><td style="text-align: center;background-color: #FFFF00;;">D4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">7/23/2011</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;;">B4</td><td style="text-align: center;;">B2</td><td style="text-align: center;;">C2</td><td style="text-align: center;;">5</td><td style="text-align: center;background-color: #FFFF00;;">C4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">7/22/2011</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;;">D5</td><td style="text-align: center;;">C9</td><td style="text-align: center;;">D3</td><td style="text-align: center;;">4</td><td style="text-align: center;background-color: #FFFF00;;">D5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">7/21/2011</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;;">C2</td><td style="text-align: center;;">B3</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">6</td><td style="text-align: center;background-color: #FFFF00;;">D2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">7/20/2011</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;background-color: #FFFF00;;">9</td><td style="text-align: center;;">B3</td><td style="text-align: center;;">D4</td><td style="text-align: center;;">D6</td><td style="text-align: center;;">7</td><td style="text-align: center;background-color: #FFFF00;;">B2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">7/19/2011</td><td style="text-align: center;background-color: #FFFF00;;">0</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;;">D10</td><td style="text-align: center;;">C4</td><td style="text-align: center;;">C1</td><td style="text-align: center;;">8</td><td style="text-align: center;background-color: #FFFF00;;">D9</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">7/18/2011</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: center;;">D3</td><td style="text-align: center;;">C6</td><td style="text-align: center;;">B3</td><td style="text-align: center;;">9</td><td style="text-align: center;background-color: #FFFF00;;">B1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;background-color: #FFFF00;;">C1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;background-color: #FFFF00;;">B4</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=OFFSET(<font color="Blue">INDIRECT(<font color="Red">I2</font>),ROW(<font color="Red">$I$2</font>)-1,COLUMN(<font color="Red">$B$2</font>)-2</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[CARBOB]

I think you got it, let me work with it, because column I has more values than shown. Appreciate this very much, thank you!!!

 

[markmzz]

I think you got it, let me work with it, because column I has more values than shown. Appreciate this very much, thank you!!!

CARBOB,

Do some tests with the formula and tell us if it work.

Markmzz