create formula dependant on 2 variables each with multiple options

andrewdelmont

New Member
Joined
Apr 27, 2008
Messages
18
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top