Help with possible Index Match Match?

[TheCrankyMule]

Hi All,

This may be an impossible task so bear with me. I have the following raw data:

Owner	Account ID	Industry	Industry#	Account #
Joe	1	Plumber	Primary Industry	Primary Account
Joe	2	HVAC	Secondary Industry	Secondary Account
Bob	3	Plumber	Primary Industry	Primary Account
Bob	4	Electrician	Secondary Industry	Secondary Account
Bob	5	Construction	Tertiary Industry	Tertiary Account

<tbody>
</tbody>

Then I need to append it to this table:

Owner	Primary Industry	Secondary Industry	Tertiary Industry	Primary Account	Secondary Account	Tertiary Account
Joe
Bob

<tbody>
</tbody>

So that it ends up looking like this:

Owner	Primary Industry	Secondary Industry	Tertiary Industry	Primary Account	Secondary Account	Tertiary Account
Joe	Plumber	HVAC		1	2
Bob	Plumber	Electrician	Construction	3	4	5

<tbody>
</tbody>

I was thinking I could use an index match match, but I'm not able to make it work. Anyone have any solutions? Maybe I need to rearrange the data? It's tough, because I'm writing SQL to pull the raw data the way it is, so I'm a bit limited in how I pull it. Thanks so much for your help.

 

[steve the fish]

Hi. Try:

=IFERROR(IF(ISNUMBER(SEARCH("industry",B$9)),INDEX($C$2:$C$6,MATCH(1,INDEX(($D$2:$D$6=B$9)*($A$2:$A$6=$A10),0),0)),INDEX($B$2:$B$6,MATCH(1,INDEX(($E$2:$E$6=B$9)*($A$2:$A$6=$A10),0),0))),"")

 

[Fluff]

Hi & welcome to MrExcel
How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Owner	Account ID	Industry	Industry#	Account #		Owner	Primary Industry	Secondary Industry	Tertiary Industry	Primary Account	Secondary Account	Tertiary Account
2	Joe	1	Plumber	Primary Industry	Primary Account		Joe	Plumber	HVAC		1	2
3	Joe	2	HVAC	Secondary Industry	Secondary Account		Bob	Plumber	Electrician	Construction	3	4	5
4	Bob	3	Plumber	Primary Industry	Primary Account
5	Bob	4	Electrician	Secondary Industry	Secondary Account
6	Bob	5	Construction	Tertiary Industry	Tertiary Account
Sheet6

Cell Formulas
Range	Formula
H2	{=IFERROR(INDEX($C$2:$C$6,MATCH(1,($A$2:$A$6=$G2)*($D$2:$D$6=H$1),0)),"")}
K2	{=IFERROR(INDEX($B$2:$B$6,MATCH(1,($A$2:$A$6=$G2)*($E$2:$E$6=K$1),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

fill both formulae across & down

 

[TheCrankyMule]

Wow. Thanks for this. I've used many array formulas when doing Index Match with multiple criteria, but I've never seen id quite this way before. Would you possibly be able to explain exactly how the Match() funtion is working in this formula? Otherwise I would appreciate it if you could point me to a resource that could help. Thanks again!

 

[Fluff]

If we consider the formula in H2
this ($A$2:$A$6=$G2) will return either true or false depending on if the values in A2:A6 equal G2 ie
TRUE;TRUE;FALSE;FALSE;FALSE
this ($D$2:$D$6=H$1) will do the same for col D & H1
TRUE;FALSE;TRUE,FALSE,FALSE
the * symbol coerces them to 1 where both are TRUE & 0 for the rest giving
1;0;0;0;0
The MATCH then matches the first 1 it finds (in the case the first value in the array)
so the formula then returns the 1st value in col C.

Something I find useful is to use "Evaluate Formula" on the formula tab.

HTH