Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup help
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup help
You don't need a lookup formula on sheet2.. Try this:
=IF(Sheet1!A14%120%,IF(Sheet1!A12%,90%,80%)) -- Biff Microsoft Excel MVP "Curtis" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup help
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Curtis" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup help
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%,8 0%,IF(VLOOKUP(A1,Sheet2!$A$10:$I$24,9,"false")4%, 120%,90%)) Regards, Paul -- "Curtis" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup help
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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Curtis" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup help
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 "PCLIVE" wrote: 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%,8 0%,IF(VLOOKUP(A1,Sheet2!$A$10:$I$24,9,"false")4%, 120%,90%)) Regards, Paul -- "Curtis" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |