Vexxing Lookup Problem

Hi All,
I have been struggling with this lookup problem for a few days now and am stuck...

I have a list of employees who's job status may change at the end of a given month. If the status changes, they have a supplemental payment that is tied to their job status and may change as well. But that depends upon certain conditions like their years of service and their union.

I'm trying to write a formula that will update that supplemental payment based upon certain conditions, but I can't quite seem to get it right. A challenge is the table I'm looking values up in has 8 columns and the value could be in any of the columns depending on years of service.

These are the conditions for the lookup:
1. If their union local is the same, they would keep the supplemental payment so long as it is not more than allowed for the title. If it is more, they get the supplemental that corresponds to the new title and the years of service.

2. If the new title is PAA and they have 10 or more years of service, the new supplmental is $600.00

3. If the new title is Clerical Associate II and they have a 6 or more years of service, they get a supplemental that corresponds to their years of service.

The formula I have now doesn't get the job done. It is:

Please Login or Register  to view this content.

Any ideas? I've been stuck on this for a couple days so I'd really appreciate any help I can get! I have attached the spreadsheet.

Thanks in advance

Cheers,
Tim

Well, I don't have all your logic worked out, but I know you're using the VLOOKUP incorrectly.

The value you're searching for has to be in the FIRST column of the lookup range. Your named range "table_atg" has "Longevity" as the first column, so the job classes you're searching for in column N will never be found.

Change the named range to start in column C (Differential), and you'll be closer.

To add in a check for numbers of years of service, you can try a MATCH() function.

IN fact, I would probably switch to an INDEX(TableRange,Match,Match) approach on this whole thing.

TableRange = Sheet2!F3:N31
Jobs = Sheet2!C3:C31
YrsSrvc = Sheet2!F2:N2

Now you can spot an dollar value in the TableRange like so:

=INDEX(TableRange, MATCH(N2, Jobs, 0), MATCH(C2, YrsSrvc, 1))

PAA I - III all have $600 starting at year 10, but if that continues in the later tiers of the TableRange, you'll need to fill it in to the right, in the columns for 12, 15, 18 and 20. The same goes for all the empty cells.

First, get it working with simple formulas. This will help you spot errors in the data itself, frequently caused by extra spacing in the data or in the table.

Once you get the basic formula to return simple lookup values, then start to expand it to include your other fancier criteria.

Feel free to repost the doc.