[SOLVED] Vexxing Lookup Problem

judasdac · #1 07-01-2009, 12:01 PM

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:

Code:

=IF(AND(Q2=F2,H2<VLOOKUP(N2,table_atg,13,FALSE),),H2,IF(AND(N2="PAA*",Sheet1!C2>=10,),"$600.00",IF(AND(N2="clerical assoc II",C2>=6,),VLOOKUP(N2,table_atg,7,FALSE),H2)))

JBeaucaire · #2 07-01-2009, 01:16 PM

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.

judasdac · #3 07-01-2009, 01:52 PM

Hi JB --- thanks for the response and helping me sort through this. I had actually thought the index and match method might work better than doing a vlookup, but I hadn't been able to wrap my head around it.

The logic behind your formula seems solid, but I'm coming up with "#N/A" and "#Name" errors. The ranges have been renamed so I'm puzzled as to what could be causing that.

JBeaucaire · #4 07-01-2009, 02:16 PM

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.

judasdac · #5 07-01-2009, 04:03 PM

Thanks, JB! After following your advice, and working through the formula again I realized that I had named the years of service range "YrsSvc" and did not adjust the formula. After correcting it, this is working beautifully :-)

Very happy to be able to move on from this one piece of the puzzle

Welcome to the Excel Forum

Forum Statistics