IF Statement using a named list or a table on a seperate sheet to return a specified value

[JennOlsen]

Hello!

What I'm trying to do is minimize some of my work here.

I have a list of employees with pay and pay grade. I need to deterimine if they have reached the max for that pay grade. I have the pay grade code, and the max pay on a seperate sheet. I have not yet named the range, but could if it would simply things.

What I want to do in a new column on Sheet1, is write a formula that tell excel to look at the code in column J, reference the table on Sheet2, column A for a match, and return the value listed on Sheet2, column B.

If it could even then tell me that the value in column I on Sheet1 is equal or greater than the value of ColumnB, Sheet2 with a "yes" or "no", (instead of the value) that would be even better!

[judgeh59]

Is it possible to upload a dummy file....it's alot easier with a file....of course, remove any company confidential info...

[ImStevenB]

Hi Jenn,

If I am understanding you correctly, I have uploaded a sample file for you to look at. You will need to modify the formula to fit your sheets.

Basically, what I did on the sample is on Sheet1 - Column B is the pay grade - Column C represents the current salary level.

On sheet 2 is a matrix with the pay grade, starting salary and max salary.

On sheet1 in cell D4 you will see this formula:

Please Login or Register  to view this content.

What the formula does is look at the pay grade in B4 and performs a lookup for the paygrade in the range on Sheet2. If the amount on sheet2 is greater than the current salary, then they have not reached the max and it fills in with a No .. otherwise it returns a Yes.

Hope this helps. Let me know.

SampleSalaryReference.xlsx

[JennOlsen]

Hi Jenn,

If I am understanding you correctly, I have uploaded a sample file for you to look at. You will need to modify the formula to fit your sheets.

Basically, what I did on the sample is on Sheet1 - Column B is the pay grade - Column C represents the current salary level.

On sheet 2 is a matrix with the pay grade, starting salary and max salary.

On sheet1 in cell D4 you will see this formula:

Please Login or Register  to view this content.

What the formula does is look at the pay grade in B4 and performs a lookup for the paygrade in the range on Sheet2. If the amount on sheet2 is greater than the current salary, then they have not reached the max and it fills in with a No .. otherwise it returns a Yes.

Hope this helps. Let me know.

Attachment 304470

This *almost* works. I have two problems with it.

1. I need it to compare if it equals or not as well. If the value in cell I4 on my sheet, is greater than or equal to the value of the paytable, I need it to return a "Yes".
2. If I copy this formula down through all my rows, it is changing the vlookup reference array, which is not something I want it to do.

Is it possible to upload a dummy file....it's alot easier with a file....of course, remove any company confidential info...

I am working on a sample file as well.

[JennOlsen]

Sample.xlsx Sample of my book

[ImStevenB]

I guess I have the Condition statement backwards then.

Using your sample workbook I changed my formula to this:

Please Login or Register  to view this content.

And it adjusts correctly when I change the data on sheet 1 and when I copy it down the rows.

Hope this helps.

[JennOlsen]

I guess I have the Condition statement backwards then. What is the sheet name and array of the VLOOKUP?

Please Login or Register  to view this content.

That formula is the one I used in your sample.

How do I prevent the range A2:C10 from changing when doing a drag down to copy the formula? The other cell references should change, but the range should not.

[ImStevenB]

I forgot the $ sign in my earlier reply .. I updated it.

the $A$2:$C$10 keeps the cell reference as you copy it

[JennOlsen]

I forgot the $ sign in my earlier reply .. I updated it.

the $A$2:$C$10 keeps the cell reference as you copy it

Great! That seems to work perfectly.

Thanks!

[ImStevenB]

You're Welcome

Happy to help.

Don't forget to mark the thread solved.