vlookup
 

I have a spreadsheet that has employees with pay grades, SR01 all the way
through SR15. I have a vlookup that looks for this in my paygrade range that
has SR01 next column the amt. (=vlookup,b2,paygrade,2).
The problem, I have some top officials listed, but with a pay grade not in
my pay grade range so it brings back the last one, so if I have SR15 $1980
as the last one in the range, and an official with a pay grade range SR18 it
brings back the last one in my list which is the amt for SR15. Is there
something else I need to add to the vlookup formula that if it doesn't find a
match to bring back a N/A or 0?
Thanks,

vlookup
 

Try,
=VLOOKUP(B2,paygrade,2,FALSE)

False ensures it only finds exact matches

Mike

"Pammy" wrote:

I have a spreadsheet that has employees with pay grades, SR01 all the way
through SR15. I have a vlookup that looks for this in my paygrade range that
has SR01 next column the amt. (=vlookup,b2,paygrade,2).
The problem, I have some top officials listed, but with a pay grade not in
my pay grade range so it brings back the last one, so if I have SR15 $1980
as the last one in the range, and an official with a pay grade range SR18 it
brings back the last one in my list which is the amt for SR15. Is there
something else I need to add to the vlookup formula that if it doesn't find a
match to bring back a N/A or 0?
Thanks,

vlookup
 

Try modifying formula to include the last option.........

=vlookup(b2,paygrade,2,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Pammy" wrote:

I have a spreadsheet that has employees with pay grades, SR01 all the way
through SR15. I have a vlookup that looks for this in my paygrade range that
has SR01 next column the amt. (=vlookup,b2,paygrade,2).
The problem, I have some top officials listed, but with a pay grade not in
my pay grade range so it brings back the last one, so if I have SR15 $1980
as the last one in the range, and an official with a pay grade range SR18 it
brings back the last one in my list which is the amt for SR15. Is there
something else I need to add to the vlookup formula that if it doesn't find a
match to bring back a N/A or 0?
Thanks,

vlookup
 

If you want something a bit more informative try:-

=IF(ISNA(VLOOKUP(B2,paygrade,2,FALSE)),"That salary grade cannot be
found",VLOOKUP(B2,paygrade,2,FALSE))

Mike

"Pammy" wrote:

I have a spreadsheet that has employees with pay grades, SR01 all the way
through SR15. I have a vlookup that looks for this in my paygrade range that
has SR01 next column the amt. (=vlookup,b2,paygrade,2).
The problem, I have some top officials listed, but with a pay grade not in
my pay grade range so it brings back the last one, so if I have SR15 $1980
as the last one in the range, and an official with a pay grade range SR18 it
brings back the last one in my list which is the amt for SR15. Is there
something else I need to add to the vlookup formula that if it doesn't find a
match to bring back a N/A or 0?
Thanks,