Use Index Match to fill in columns Sheet 1 based on data in Sheet 2

[roba730]

I have Sheet 1 with an account number in column a and column B-Y are different products that each account may own. I would like to have the sheet filled out as seen below. I think I will be able to fill in the X's using Index Match.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Account#[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: right"]14331937[/TD]

</tbody>

[/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR="class: grid"]
[TD="width: 84, align: right"]14346609[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





I have a second sheet in which each row contains an account number in Column A and a product they own in Column B. Each account number may own multiple products which will be found on multiple rows.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Account#[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]14331937[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]14331937[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]14331937[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]14331937[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR="class: grid"]
[TD="width: 84"]14346609[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR="class: grid"]
[TD="width: 84"]14346609[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR="class: grid"]
[TD="width: 84"]14346609[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR="class: grid"]
[TD="width: 84"]14346609[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR="class: grid"]
[TD="width: 84"]14346609[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]14346609[/TD]
[TD]W[/TD]
[/TR]
</tbody>[/TABLE]
I have never used Index Match.

Thanks in Advance for your help.

 

[CodeNinja]

You could use the following formula in sheet 1 cell B2... copy over to Z2 and down...

=IF(SUMPRODUCT((Sheet2!$A$3:$A$12=Sheet1!$A2)*(Sheet2!$B$3:$B$12=Sheet1!B$1)),"x","")

Good luck,
CN.

 

[roba730]

You could use the following formula in sheet 1 cell B2... copy over to Z2 and down...

=IF(SUMPRODUCT((Sheet2!$A$3:$A$12=Sheet1!$A2)*(Sheet2!$B$3:$B$12=Sheet1!B$1)),"x","")

Good luck,
CN.

Thank you CN this works.

To help me understand how it does work I have a question; does the * in the formula mean AND or MULTIPLY?

Thx again, Rob

 

[CodeNinja]

To help me understand how it does work I have a question; does the * in the formula mean AND or MULTIPLY?

Well, that depends on how you look at it...

The sumproduct formula is special because it handles arrays.
The first part (Sheet2!$A$3:$A$12=Sheet1!$A2) results in Nine Trues or Falses.
The second part (Sheet2!$B$3:$B$12=Sheet1!B$1)) also results in 9 Trues or Falses.

The * literally means multiply... so we multiply an array of 9 trues or falses against another array of 9 trues or falses... this action turns into an array of 1's or 0's....
IE {True, True, False, False}*{True, False, True, False} would = {1, 0, 0, 0}
So in essence, you can think of it as an AND ... so TRUE AND TRUE would be 1, but TRUE AND FALSE, FALSE AND FALSE, or FALSE AND TRUE would all = 0...

The answer is, you can think of it both ways ... It does mean "AND"... it also does mean Multiply

Hope that hasn't made this more confusing...

CN.

 

[Mike Szczesny]

Hi,
Can you use something like this?
Mike Szczesny

Excel 2012
	A	B	C	D	E	F	G	H
1		a	b	c	d	e	f	g
2	i		x		x	x	x
3	ii	x		x				x
4
5
6	i	b
7		d
8		e
9		f
Sheet1

Cell Formulas
Range	Formula
B6	{=INDEX($B$1:$H$1,SMALL(IF($A$2:$A$3=$A$6,IF($B$2:$H$3="x",COLUMN($B$1:$H$1)-COLUMN($B$1)+1)),ROWS($B$6:B6)))}
Press CTRL+SHIFT+ENTER to enter array formulas.