ExcelBanter - Vlookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Vlookup (https://www.excelbanter.com/excel-worksheet-functions/182732-vlookup.html)

Derek M

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

dennis

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

Mike H

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

Derek M[_2_]

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

Derek M[_2_]

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

Derek M[_2_]

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

dennis

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

Mike H

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

All times are GMT +1. The time now is 05:35 PM.