+ Reply to Thread
Results 1 to 5 of 5

Vexxing Lookup Problem

  1. #1
    Registered User
    Join Date
    11-14-2008
    Location
    Brooklyn, NY, USA
    Posts
    53

    Smile 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
    Attached Files Attached Files
    Last edited by judasdac; 07-01-2009 at 04:03 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vexxing Lookup Problem

    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.

  3. #3
    Registered User
    Join Date
    11-14-2008
    Location
    Brooklyn, NY, USA
    Posts
    53

    Re: Vexxing Lookup Problem

    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.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vexxing Lookup Problem

    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.

  5. #5
    Registered User
    Join Date
    11-14-2008
    Location
    Brooklyn, NY, USA
    Posts
    53

    Re: Vexxing Lookup Problem

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07003 seconds
  • Memory Usage 8,955KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (5)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (5)postbit_legacy
  • (5)postbit_onlinestatus
  • (5)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (48):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1