Vlookup help

[Curtis]

Sheet 1
cell a1 = vlookup data
cell b1 is where I want Vlook up results...curent formla is
=VLOOKUP(A1,'sheet2'!$A$10:$I$24,9,"false")

Sheet 2
vlookup range = 'sheet2'!$A$10:$I$24
Need a formula that will vlookup cell a1 in sheet one and if that number is
less than or equal to 2% place a value of 80% in cell a1. If the number is
greater than 2 % but less than or eqaul to 4%, place a value of 90% in cell
A1, and if the number is greater than 4%, place a value of 120%

hope that makes sense

Thanks

 

[T. Valko]

You don't need a lookup formula on sheet2.. Try this:

=IF(Sheet1!A1>4%120%,IF(Sheet1!A1>2%,90%,80%))

 

[Bernard Liengme]

A couple of nit-picking questions:
1) I hope the formula is =VLOOKUP(A1,'sheet2'!$A$10:$I$24,9,FALSE)
without the quotes
2) This formula is in B1 so the results will be in B1 not in A1
So QUOTE less than or equal to 2% place a value of 80% in cell a1 QUOTE
should reference B1 not A1
3) Why not add a column H, starting in H10 with =IF(H10<=2%,90%,
IF(H10<=4%,80%, 120%)) and copy down to H24
or use on sheet1
=IF(your-formula<=2%,90%, IF(your-formula<=4%,80%, 120%))
I do hope I have understood your need
best wishes

 

[PCLIVE]

If you want to do your calculations all in one shot, then maybe something
like this.

=IF(VLOOKUP(A1,Sheet2!$A$10:$I$24,9,"false")<=2%,80%,IF(VLOOKUP(A1,Sheet2!$A$10:$I$24,9,"false")>4%,120%,90%))

Regards,
Paul

 

[Curtis]

1) yes
2)yes

sheet 1 column a contains sales rep IDs (a1:a12)
sheet 2 contains reps and YTD sales % achevied ('sheet2'!$A$10:$I$24)
I need to look for each rep in sheet 2 and determine their commision rate as
follows

if that number is in range 'sheet2'!$A$10:$I$24 is less than or equal to 2%
place a value of 80% in sheet1 cell a1. If the number is greater than 2 % but
less than or eqaul to 4%, place a value of 90% in sheet 1 cell A1, and if the
number is greater than 4%, place a value of 120%.

for example if a rep sells 2.3% their comm would be 90%

also the % (2%, 2-4%, 4%) need to be dynamic so the formaula has to contain
the location of the value and not the value....located in sheet 2 say d7, e7,
and f7

hope that makes sense

 

[Curtis]

I have just reqlized that I did not provide you the correct infomation to
assist. The correct info is below

Sheet 1 is my summary page
Column A contains sales reps ID (a1= 1234)
Column B will contain the required formula that will determine commission
results

Sheet 2 is the source page
Vlookup range is 'sheet2'!$A$10:$I$24
Cell D3 contains target of 7.8% (this value could can change YOY)
Cell E3 contains target of 7.3%
Cell F3 contains target of 6.5%

Need a formula that will vlookup 'sheet2'!$A$10:$I$24 and if that number is
less than or equal to 6.5% place a value of 200 % in Sheet 1 cell B1. If the
number is
greater than 6.5% but less than or eqaul to 7.3%, place a value of 100% in
Sheet 1 cell
B1, if the number is greater than 7.3% but less than 7.8% place a value of
80% and lastly if the number is greater than 7.8% place a 0%


sry and thanks