Can Excel Rtrn a Value by Referencing Multiple Rows and Columns

I have a reference table which contains multiple commission %'s in 21 columns
labeled with 3 letter codes; with rows of commission sources in column A.
Each commission source in column A has multiple %'s payable based on the
three letter code or type. I need Excel to look at a row on a daily data
sheet which contains a commission source in C and a three letter % code in F,
then go to the table, find the intersection of those two references and
return the valid % to the current data sheet. The ultimate purpose is to
recalulate the commission %'s received (daily data sheet) against contracted
%'s (reference table) and find variances based on commission source and type.

Hi,

It would be nice to see a sample data layout. However, if you range name
the columns containing the 3 letter code with those 3 letter names and the
rows with the commision source names and you enter the source in C1 and the 3
letter code in F1 then

=INDIRECT(C1) INDIRECT(F1)

will return the % at the intersection of the two references.
To name all the ranges select your table including the 3 letter codes at the
top and the commission source column on the left and choose Insert, Name,
Create.

Note the above formula is in one cell and has a space between the two
INDIRECT functions, the space is Excel's intersect operator.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Kim" wrote:

I have a reference table which contains multiple commission %'s in 21 columns
labeled with 3 letter codes; with rows of commission sources in column A.
Each commission source in column A has multiple %'s payable based on the
three letter code or type. I need Excel to look at a row on a daily data
sheet which contains a commission source in C and a three letter % code in F,
then go to the table, find the intersection of those two references and
return the valid % to the current data sheet. The ultimate purpose is to
recalulate the commission %'s received (daily data sheet) against contracted
%'s (reference table) and find variances based on commission source and type.

Thanks Shane,

Below are samples of the worksheets involved. I need a formula in the
"Contract %" column on the "Daily Data" sheet which will return a value from
the "Source Table" for the "Source" and "Type" listed in the row on the daily
data sheet. There are 50 "Sources" and 21 columns of possible "Types".

Daily Data
Date Source Type Gross Com Com% Contract %

6/1/09 California EXE 10.00 2.80 28.00%
6/1/09 Florida SPR 14.00 3.92 28.00%
6/1/09 Michigan TRF 2.00 0.56 28.00%
6/1/09 California SPR 11.00 3.08 28.00%
6/1/09 Florida EXE 2.00 0.56 28.00%
6/1/09 Michigan EXE 11.00 3.08 28.00%
6/1/09 New York SPR 2.00 0.56 28.00%

Contract Commissions Table
Com SourceEXE SPR TRF
California 18.00% 21.00% 21.00%
Florida 17.00% 20.50% 20.50%
Michigan 17.00% 20.50% 20.50%
New York 18.00% 24.00% 24.00%

I would be so grateful if you can help. The daily data comes in with 8000
lines per week and it would be a nightmare to hand-key those contract %'s for
comparison.

Cheers to you as well,
Kim



"Shane Devenshire" wrote:

Hi,

It would be nice to see a sample data layout. However, if you range name
the columns containing the 3 letter code with those 3 letter names and the
rows with the commision source names and you enter the source in C1 and the 3
letter code in F1 then

=INDIRECT(C1) INDIRECT(F1)

will return the % at the intersection of the two references.
To name all the ranges select your table including the 3 letter codes at the
top and the commission source column on the left and choose Insert, Name,
Create.

Note the above formula is in one cell and has a space between the two
INDIRECT functions, the space is Excel's intersect operator.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Kim" wrote:

I have a reference table which contains multiple commission %'s in 21 columns
labeled with 3 letter codes; with rows of commission sources in column A.
Each commission source in column A has multiple %'s payable based on the
three letter code or type. I need Excel to look at a row on a daily data
sheet which contains a commission source in C and a three letter % code in F,
then go to the table, find the intersection of those two references and
return the valid % to the current data sheet. The ultimate purpose is to
recalulate the commission %'s received (daily data sheet) against contracted
%'s (reference table) and find variances based on commission source and type.