I am trying to build an input calculator that will return the values based
on certain inputs.

I have an excel grid that give customer characteristics:

Customer Rating transaction amount Approval Authority Approval Level Required
Column A
7 to 10 $0 $0
6 $10,000 $10,000
5 $15,000 $25,000
4 $6,000 $31,000
3 $0 $31,000
2 $0 $31,000
1 $0 $31,000
Is there a new customer involved?

i have created a look up table that has the customer rating and the
transaction high level per tier and want to return the required approval
level in column D

my lookup table for customer level 6 is on sheet 2:

column A (answers to lookup approval)
column B (Customer level)
column C ( up to approval value)

Associate + PCA/PCM or Custom Specialist 6 $1,000
Manager + Specialist 6 $3,000
Executive + ME or CCS 6 $5,000
Approval Executive + ME or CE 6 $30,000
Senior EXECUTIVE + President 6 $60,000
Risk Executive + President 6 60+

what would the proper if formula look like?

Hi Belinda

I am not sure that I understand the layout of your data table.
Would you provide an example for both what it look like in Sheet 1 and 2.
and what is the expected result.

I think either Vlookup or Index Match would be the solution.

HTH

--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Belinda7237" wrote:

I am trying to build an input calculator that will return the values based
on certain inputs.

I have an excel grid that give customer characteristics:

Customer Rating transaction amount Approval Authority Approval Level Required
Column A
7 to 10 $0 $0
6 $10,000 $10,000
5 $15,000 $25,000
4 $6,000 $31,000
3 $0 $31,000
2 $0 $31,000
1 $0 $31,000
Is there a new customer involved?

i have created a look up table that has the customer rating and the
transaction high level per tier and want to return the required approval
level in column D

my lookup table for customer level 6 is on sheet 2:

column A (answers to lookup approval)
column B (Customer level)
column C ( up to approval value)

Associate + PCA/PCM or Custom Specialist 6 $1,000
Manager + Specialist 6 $3,000
Executive + ME or CCS 6 $5,000
Approval Executive + ME or CE 6 $30,000
Senior EXECUTIVE + President 6 $60,000
Risk Executive + President 6 60+

what would the proper if formula look like?

Sheet 1:

customer rating transaction amount Approval Level Required
6 $10,000


Sheet 2
Approval Authority customer rating trans $
assoc. 6 $1,000
assoc. + mgr 6 $3,000
Exec 6 $5,000
Assoc. + Exec 6 $15,000


On sheet 1 the customer rating is 6(column A) and the transaction amount is
10,000 (column c) and in column D I want to return a value from the look up
table for an approval requirement.

On sheet 2
in column A i have the approval value that i need to input into sheet 1
It will no what row i need by matching column B on sheet 2 which is the
customer rating and column C which is the tranaction amount.

In this example the rating is 6 and the transaction amount is 10,000 - the
values in the lookup table go from 5000 on row 3 to 15000 on row 4 so 10000
would need to look at the 15000 level and provide the answer that matches the
15000 which is Assoc +Exec.

thinking the same.
I do not understand what you are needing a =if
type formula for
what is concept you are trying to achieve.
IF you type
1234 st, the city st and zip will populate to next cell
is the need for your formula or is it more complex that that

"Belinda7237" wrote:

I am trying to build an input calculator that will return the values based
on certain inputs.

I have an excel grid that give customer characteristics:

Customer Rating transaction amount Approval Authority Approval Level Required
Column A
7 to 10 $0 $0
6 $10,000 $10,000
5 $15,000 $25,000
4 $6,000 $31,000
3 $0 $31,000
2 $0 $31,000
1 $0 $31,000
Is there a new customer involved?

i have created a look up table that has the customer rating and the
transaction high level per tier and want to return the required approval
level in column D

my lookup table for customer level 6 is on sheet 2:

column A (answers to lookup approval)
column B (Customer level)
column C ( up to approval value)

Associate + PCA/PCM or Custom Specialist 6 $1,000
Manager + Specialist 6 $3,000
Executive + ME or CCS 6 $5,000
Approval Executive + ME or CE 6 $30,000
Senior EXECUTIVE + President 6 $60,000
Risk Executive + President 6 60+

what would the proper if formula look like?

try this formula in D2 and copy down as far as your data range is.

=INDEX(Sheet2!A:A,MATCH(Sheet1!A2&TEXT(Sheet1!C2," ????0"),Sheet2!$B$2:$B$5&TEXT(Sheet2!$C$2:$C$5,"?? ??0"))+1)

Let me know if this do what you want?

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Belinda7237" wrote:

Sheet 1:

customer rating transaction amount Approval Level Required
6 $10,000


Sheet 2
Approval Authority customer rating trans $
assoc. 6 $1,000
assoc. + mgr 6 $3,000
Exec 6 $5,000
Assoc. + Exec 6 $15,000


On sheet 1 the customer rating is 6(column A) and the transaction amount is
10,000 (column c) and in column D I want to return a value from the look up
table for an approval requirement.

On sheet 2
in column A i have the approval value that i need to input into sheet 1
It will no what row i need by matching column B on sheet 2 which is the
customer rating and column C which is the tranaction amount.

In this example the rating is 6 and the transaction amount is 10,000 - the
values in the lookup table go from 5000 on row 3 to 15000 on row 4 so 10000
would need to look at the 15000 level and provide the answer that matches the
15000 which is Assoc +Exec.

Oops, forget to add that this is an array formula.
after placing the formula, confirm by Ctrl, Shift and Enter all together

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"xlm" wrote:

try this formula in D2 and copy down as far as your data range is.

=INDEX(Sheet2!A:A,MATCH(Sheet1!A2&TEXT(Sheet1!C2," ????0"),Sheet2!$B$2:$B$5&TEXT(Sheet2!$C$2:$C$5,"?? ??0"))+1)

Let me know if this do what you want?

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,









"Belinda7237" wrote:

Sheet 1:

customer rating transaction amount Approval Level Required
6 $10,000


Sheet 2
Approval Authority customer rating trans $
assoc. 6 $1,000
assoc. + mgr 6 $3,000
Exec 6 $5,000
Assoc. + Exec 6 $15,000


On sheet 1 the customer rating is 6(column A) and the transaction amount is
10,000 (column c) and in column D I want to return a value from the look up
table for an approval requirement.

On sheet 2
in column A i have the approval value that i need to input into sheet 1
It will no what row i need by matching column B on sheet 2 which is the
customer rating and column C which is the tranaction amount.

In this example the rating is 6 and the transaction amount is 10,000 - the
values in the lookup table go from 5000 on row 3 to 15000 on row 4 so 10000
would need to look at the 15000 level and provide the answer that matches the
15000 which is Assoc +Exec.