# create formula dependant on 2 variables each with multiple options

#### andrewdelmont

##### New Member
I have created a spreadsheet to run the compliance documents for our financial brokerage. On the form to change the financial adviser, I want to create a formula to automatically insert a brokers code depending on the company chosen. It must be dependant on the broker chosen as well as the company chosen. There are 6 brokers and 8 different companies.

This part I can do, but I cant add other companies or brokers

=IF(AND(E11="Andrew Delmont",B14="Momentum Life"),"780726")

Thank-you

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Jon von der Heyden

##### MrExcel MVP, Moderator
You should probably be using a lookup table of sorts, and your formula could read something like:

=INDEX(Map_Data,MATCH(B14,Map_Rows,0),MATCH(E11,Map_Cols,0))

See example attached:
Book1
ABCDEFG
1
2
3Company 1Broker 1737113
4
5
6
7
8
9
10
11
12
13Broker 1Broker 2Broker 3Broker 4Broker 5Broker 6
14Company 1737113746313795014785778748113747110
15Company 2777928746416785887752491714381725247
16Company 3755463757853754538741540708049770969
17Company 4706071739937777749785475776479761771
18Company 5709882772221758909748359709028799871
19Company 6739154725139751643755601778412719212
20Company 7704739723096740904748623750300752216
21Company 8742651710617715623718481780386744172
Temp

Formula in this example: =INDEX(\$B\$14:\$G\$21,MATCH(A3,\$A\$14:\$A\$21,0),MATCH(\$B3,\$B\$13:\$G\$13,0))

#### andrewdelmont

##### New Member
Thanks, will try set that up and let you know

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,164,093
Messages
5,835,349
Members
430,351
Latest member
ddalton

### 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.

### Which adblocker are you using?

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