create formula dependant on 2 variables each with multiple options

[andrewdelmont]

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

 

[Jon von der Heyden]

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
	A	B	C	D	E	F	G
1
2
3	Company 1	Broker 1	737113
4
5
6
7
8
9
10
11
12
13		Broker 1	Broker 2	Broker 3	Broker 4	Broker 5	Broker 6
14	Company 1	737113	746313	795014	785778	748113	747110
15	Company 2	777928	746416	785887	752491	714381	725247
16	Company 3	755463	757853	754538	741540	708049	770969
17	Company 4	706071	739937	777749	785475	776479	761771
18	Company 5	709882	772221	758909	748359	709028	799871
19	Company 6	739154	725139	751643	755601	778412	719212
20	Company 7	704739	723096	740904	748623	750300	752216
21	Company 8	742651	710617	715623	718481	780386	744172
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]

Thanks, will try set that up and let you know