assigning names based on state and industry PLEASE HELP

[alinka]

Hi excel geniuses!

I have a problem. I have a list which has a state, region, industry and assigned person for each state/industry.

The list goes like this:

STATE REGION FINANCE RETAIL MANUF
AL EAST Jack Joe Michelle
CA West Ken Ken Ken

Then I have a separate sheet where there's a column for a company name, state code, and industry.

What I want to do is add another column with Assigned Person based on the two factors: STATE and INDUSTRY.

How can I do this quickly? Is there a way to automate this? THANKS SO MUCH

 

[Tazguy37]

Re: assigning names based on state and industry

Have you looked into using a pivot table for this? I'm not an expert in them, but it seems like using one would save you a lot of headaches. You could create one, and then move fields around until you get it to look the way you want. Hope that gives you some ideas.

 

[alinka]

Re: assigning names based on state and industry

Thanks, but I'm not sure how to do that, especially since I have two separate sheets.

 

[Brian from Maui]

Like this?

Book1
	A	B	C	D	E	F	G
2	State	Region	Finance	Retail	Manuf	AL	Joe
3	AL		Jack	Joe	Michelle	Retail
4	CA		Ken	Ken	Ken
Sheet1

Here's the formula,

=INDEX($C$3:$E$4,MATCH(F2,$A$3:$A$4,0),MATCH(F3,$C$2:$E$2,0))

 

[alinka]

this formula worked perfect. THANKS SO MUCH. Does anyone know how to turn this into a macro button?

Thanks!!!