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.

Account#

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

14331937 <tbody> </tbody>

x

x

x

x

14346609 <tbody> </tbody>

x

x

x

x

x

x

<tbody>
</tbody>

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.

A	B
Account#	Product
14331937	C
14331937	R
14331937	F
14331937	Z
14346609 <tbody> </tbody>	B
14346609 <tbody> </tbody>	J
14346609 <tbody> </tbody>	N
14346609 <tbody> </tbody>	R
14346609 <tbody> </tbody>	T
14346609	W

<tbody>
</tbody>

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.