| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Hi all
I have a vlookup table shown here 3.0% £375.00 4.0% £250.00 5.0% £120.00 6.0% £90.00 7.0% £60.00 My calculation is =VLOOKUP('Diagnostics technician'!G12,'Diagnostics technician'!O8:P12,2,TRUE)+VLOOKUP('Diagnostics technician'!E12,'Diagnostics technician'!J7:K13,2,TRUE) Unfortunately i need the cell to display £120 if the percentage is over 4%, the percentage at the moment is 4.03% and is displaying £250 I hope that makes sense, can anyone help? Thank you all in advance Derek |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Can you pass your 'Diagnostics technician'!G12 or 'Diagnostics
technician'!E12 to your VLOOKUP as INT(G12+0.99) or INT(E12+0.99) "Derek M" wrote: Hi all I have a vlookup table shown here 3.0% £375.00 4.0% £250.00 5.0% £120.00 6.0% £90.00 7.0% £60.00 My calculation is =VLOOKUP('Diagnostics technician'!G12,'Diagnostics technician'!O8:P12,2,TRUE)+VLOOKUP('Diagnostics technician'!E12,'Diagnostics technician'!J7:K13,2,TRUE) Unfortunately i need the cell to display £120 if the percentage is over 4%, the percentage at the moment is 4.03% and is displaying £250 I hope that makes sense, can anyone help? Thank you all in advance Derek |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Hi,
Try this =INDEX(P8:P12,IF(ISNUMBER(MATCH(G12,O8:O12,0)),MAT CH(G12,O8:O12,0),1+MATCH(G12,O8:O12,1)))+INDEX(K7: K13,IF(ISNUMBER(MATCH(E12,J7:J13,0)),MATCH(E12,J7: J13,0),1+MATCH(E12,J7:J13,1))) Mike "Derek M" wrote: Hi all I have a vlookup table shown here 3.0% £375.00 4.0% £250.00 5.0% £120.00 6.0% £90.00 7.0% £60.00 My calculation is =VLOOKUP('Diagnostics technician'!G12,'Diagnostics technician'!O8:P12,2,TRUE)+VLOOKUP('Diagnostics technician'!E12,'Diagnostics technician'!J7:K13,2,TRUE) Unfortunately i need the cell to display £120 if the percentage is over 4%, the percentage at the moment is 4.03% and is displaying £250 I hope that makes sense, can anyone help? Thank you all in advance Derek |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Hi Dennis
Thanks for the reply, did you mean change the formula in the cell to INT(G12+0.99)? Im just starting to get the hang of excel but not quite an expert yet, if you could explain your answer i would be most grateful Thanks - Derek "Dennis" wrote: Can you pass your 'Diagnostics technician'!G12 or 'Diagnostics technician'!E12 to your VLOOKUP as INT(G12+0.99) or INT(E12+0.99) "Derek M" wrote: Hi all I have a vlookup table shown here 3.0% £375.00 4.0% £250.00 5.0% £120.00 6.0% £90.00 7.0% £60.00 My calculation is =VLOOKUP('Diagnostics technician'!G12,'Diagnostics technician'!O8:P12,2,TRUE)+VLOOKUP('Diagnostics technician'!E12,'Diagnostics technician'!J7:K13,2,TRUE) Unfortunately i need the cell to display £120 if the percentage is over 4%, the percentage at the moment is 4.03% and is displaying £250 I hope that makes sense, can anyone help? Thank you all in advance Derek |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Hi Mike
Thanks for the reply, tried it and adjusted the cells to match the 'Diagnostics technician' but it didnt work unfortunately :( "Mike H" wrote: Hi, Try this =INDEX(P8:P12,IF(ISNUMBER(MATCH(G12,O8:O12,0)),MAT CH(G12,O8:O12,0),1+MATCH(G12,O8:O12,1)))+INDEX(K7: K13,IF(ISNUMBER(MATCH(E12,J7:J13,0)),MATCH(E12,J7: J13,0),1+MATCH(E12,J7:J13,1))) Mike "Derek M" wrote: Hi all I have a vlookup table shown here 3.0% £375.00 4.0% £250.00 5.0% £120.00 6.0% £90.00 7.0% £60.00 My calculation is =VLOOKUP('Diagnostics technician'!G12,'Diagnostics technician'!O8:P12,2,TRUE)+VLOOKUP('Diagnostics technician'!E12,'Diagnostics technician'!J7:K13,2,TRUE) Unfortunately i need the cell to display £120 if the percentage is over 4%, the percentage at the moment is 4.03% and is displaying £250 I hope that makes sense, can anyone help? Thank you all in advance Derek |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Oooh now wait, i put it in the same spreadsheet as 'Diagnostics technician'
and it works, ill copy it where it should go, thanks Mike :) "Derek M" wrote: Hi Mike Thanks for the reply, tried it and adjusted the cells to match the 'Diagnostics technician' but it didnt work unfortunately :( "Mike H" wrote: Hi, Try this =INDEX(P8:P12,IF(ISNUMBER(MATCH(G12,O8:O12,0)),MAT CH(G12,O8:O12,0),1+MATCH(G12,O8:O12,1)))+INDEX(K7: K13,IF(ISNUMBER(MATCH(E12,J7:J13,0)),MATCH(E12,J7: J13,0),1+MATCH(E12,J7:J13,1))) Mike "Derek M" wrote: Hi all I have a vlookup table shown here 3.0% £375.00 4.0% £250.00 5.0% £120.00 6.0% £90.00 7.0% £60.00 My calculation is =VLOOKUP('Diagnostics technician'!G12,'Diagnostics technician'!O8:P12,2,TRUE)+VLOOKUP('Diagnostics technician'!E12,'Diagnostics technician'!J7:K13,2,TRUE) Unfortunately i need the cell to display £120 if the percentage is over 4%, the percentage at the moment is 4.03% and is displaying £250 I hope that makes sense, can anyone help? Thank you all in advance Derek |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
I was assuming that the values in 'Diagnostics technician'!G12 or
'Diagnostics technician'!E12 or both are 4.03 in which case your VLOOKUP should be =VLOOKUP(INT('Diagnostics technician'!G12+0.99),'Diagnostics technician'!O8:P12,2,TRUE) etc... "Derek M" wrote: Hi Dennis Thanks for the reply, did you mean change the formula in the cell to INT(G12+0.99)? Im just starting to get the hang of excel but not quite an expert yet, if you could explain your answer i would be most grateful Thanks - Derek "Dennis" wrote: Can you pass your 'Diagnostics technician'!G12 or 'Diagnostics technician'!E12 to your VLOOKUP as INT(G12+0.99) or INT(E12+0.99) "Derek M" wrote: Hi all I have a vlookup table shown here 3.0% £375.00 4.0% £250.00 5.0% £120.00 6.0% £90.00 7.0% £60.00 My calculation is =VLOOKUP('Diagnostics technician'!G12,'Diagnostics technician'!O8:P12,2,TRUE)+VLOOKUP('Diagnostics technician'!E12,'Diagnostics technician'!J7:K13,2,TRUE) Unfortunately i need the cell to display £120 if the percentage is over 4%, the percentage at the moment is 4.03% and is displaying £250 I hope that makes sense, can anyone help? Thank you all in advance Derek |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Often a good idea to put things where they should be!! I'm pleased that
worked and thanks for the feedback. Mike "Derek M" wrote: Oooh now wait, i put it in the same spreadsheet as 'Diagnostics technician' and it works, ill copy it where it should go, thanks Mike :) "Derek M" wrote: Hi Mike Thanks for the reply, tried it and adjusted the cells to match the 'Diagnostics technician' but it didnt work unfortunately :( "Mike H" wrote: Hi, Try this =INDEX(P8:P12,IF(ISNUMBER(MATCH(G12,O8:O12,0)),MAT CH(G12,O8:O12,0),1+MATCH(G12,O8:O12,1)))+INDEX(K7: K13,IF(ISNUMBER(MATCH(E12,J7:J13,0)),MATCH(E12,J7: J13,0),1+MATCH(E12,J7:J13,1))) Mike "Derek M" wrote: Hi all I have a vlookup table shown here 3.0% £375.00 4.0% £250.00 5.0% £120.00 6.0% £90.00 7.0% £60.00 My calculation is =VLOOKUP('Diagnostics technician'!G12,'Diagnostics technician'!O8:P12,2,TRUE)+VLOOKUP('Diagnostics technician'!E12,'Diagnostics technician'!J7:K13,2,TRUE) Unfortunately i need the cell to display £120 if the percentage is over 4%, the percentage at the moment is 4.03% and is displaying £250 I hope that makes sense, can anyone help? Thank you all in advance Derek |
| 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 | |||
Linear Mode
