+ Reply to Thread
Results 1 to 6 of 6

Struggling with a Lookup Problem

  1. #1
    Registered User
    Join Date
    10-01-2020
    Location
    Washington, DC
    MS-Off Ver
    365
    Posts
    3

    Struggling with a Lookup Problem

    Greetings all!

    I have the following spreadsheet into which data is transferred from a database.

    TestSheet.png

    I have a Pay Table that must locate an employee's pay rate in an array (R4C6:R8C11) based on their name and year. The "Year" is updated from a database and so is the employee's name and hours in the Pay Table. I need to fill in R13C3:R19C3 by finding the employee's name in Column 4 and then matching their name in the Rate Lookup Table and bring down the proper rate for a given year. For example, I need to identify the employee's name as "Jim" then find "Jim" in the Rate Lookup Table and locate the appropriate rate by identifying the year ("2") and capturing the value (47.16).

    I've tried several combinations of VLOOKUP, INDEX/MATCH, and IF(ISNUMBER(SEARCH))) functions and can't get them to work getting either NAME or VALUE errors.

    I'm using the latest Office 365 version of Excel.

    Anyone have any hints/strategies for doing this? Thanks in advance!

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Struggling with a Lookup Problem

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Registered User
    Join Date
    10-01-2020
    Location
    Washington, DC
    MS-Off Ver
    365
    Posts
    3

    Re: Struggling with a Lookup Problem

    I've posted a sheet of representative data for review... I'm stumped and appreciate the help.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Struggling with a Lookup Problem

    C13=IFERROR(INDEX($G$5:$K$8,MATCH($D13,$F$5:$F$8,0),MATCH($D$4,$G$4:$K$4,0)),"")

    Copy down

  5. #5
    Registered User
    Join Date
    10-01-2020
    Location
    Washington, DC
    MS-Off Ver
    365
    Posts
    3

    Re: Struggling with a Lookup Problem

    That's really something - works like a charm! I would have been struggling with this for weeks without thinking of this solution.

    Very grateful for your efforts. Now I just have to sort it how it all works so I understand.

    All best!

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Struggling with a Lookup Problem

    As because you are using O365, you can also make use of the new dynamic array formulas. In C13 you can enter:
    =XLOOKUP(D13:D19,$F$5:$F$8,XLOOKUP($D$4,G4:K4,G5:K8,"not found",0),"Not found",0)
    and you're done. No copying down needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Struggling with a lookup that returns the last non zero value from a row
    By Hinchliffe2012 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-28-2018, 09:26 AM
  2. Struggling with lookup
    By barneyrubble1965 in forum Excel General
    Replies: 1
    Last Post: 12-02-2015, 06:33 AM
  3. [SOLVED] struggling with 'lookup value in" MATCH function
    By GraemeG in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-10-2015, 08:38 AM
  4. Custom List Sort - A struggling problem
    By rakesh_sss in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-29-2012, 02:50 AM
  5. Struggling with IF
    By KClark in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2011, 04:37 AM
  6. [SOLVED] Struggling IF value
    By Rebecca in forum Excel General
    Replies: 5
    Last Post: 04-06-2006, 04:55 PM
  7. [SOLVED] Help, I'm Struggling!
    By Fybo in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-04-2005, 04:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1