Board Regular
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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Tazguy37

MrExcel MVP
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.

Board Regular
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

MrExcel MVP
Like this?
Book1
ABCDEFG
2StateRegionFinanceRetailManufALJoe
3ALJackJoeMichelleRetail
4CAKenKenKen
Sheet1

Here's the formula,

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

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

Thanks!!!

Replies
6
Views
148
Replies
0
Views
244
Replies
3
Views
274
Replies
3
Views
595
Replies
3
Views
214

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,478
Messages
5,770,321
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back