+ Reply to Thread
Results 1 to 9 of 9

index/match or vlookup question over multiple columns

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Office 2013
    Posts
    850

    index/match or vlookup question over multiple columns

    sample.xlsxGood afternoon!

    I need a formula that will look at a date in a particular cell on a worksheet titled "Payroll" and match it to a date in one of twelve columns as pictured in my attachment and then when found have the formula provide as the answer the letter in the same row located in column "O". So for instance if the date "2/06/16" was in sheet/cell "Payroll!BQ2", the formula would compare that date to my pictured chart below and would then provide me the answer "H", which was located in the same row, but in column "O". I have tried multiple if statements, but that didn't work for me, at least how I was trying it. As always, I'd be grateful for any suggestions and/or help!
    Attached Images Attached Images
    Last edited by lilsnoop; 03-06-2015 at 04:41 PM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: index/match or vlookup question over multiple columns

    am i right in saying that the day determines the code

    so can we just have a column of days 1-31 and the codes ?
    then the lookup would work easily

    rather than attach an image can you attach a spreadsheet

    Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Office 2013
    Posts
    850

    Re: index/match or vlookup question over multiple columns

    File added! Thanks etaf for looking at this!

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: index/match or vlookup question over multiple columns

    i have added a column of numbers for DAYS()
    assuming my question is correct
    am i right in saying that the day determines the code

    so can we just have a column of days 1-31 and the codes ?
    then the lookup would work easily
    see attached
    Attached Files Attached Files

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,513

    Re: index/match or vlookup question over multiple columns

    Hi lilsnoop from Michigan,

    In your attached picture all calendar day of 1 start in row 2, and you have that as a C in that row....

    How about making this a much easier formula.

    You give me a date and I'll do a =Day() formula with your date. This will tell me what day of the month it is. I'll take the Row(Day(<your date>)).

    I'd then use this number to look in your table in Column O for the letter you need.

    The Letter = Index($O$2:$O$32,Row(Day(<your date>)))

    I hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Office 2013
    Posts
    850

    Re: index/match or vlookup question over multiple columns

    That worked great! Thank you!

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: index/match or vlookup question over multiple columns

    your welcome thanks for the rep

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,303

    Re: index/match or vlookup question over multiple columns

    This will do it without any extra columns...
    =INDEX(Sheet1!$O$2:$O$32,MATCH(Payroll!FM2,OFFSET(Sheet1!$C$2,0,MATCH(EOMONTH(FM2,-1)+1,Sheet1!$C$2:$O$2,0)-1,30,1),0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,303

    Re: index/match or vlookup question over multiple columns

    Happy to help and thanks for the feedback, both of you

+ 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. VLookup or Index/match searching rows instead of columns or multiple criteria
    By Groovicles in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-16-2013, 05:00 PM
  2. [SOLVED] vlookup with multiple criteria (SUMPRODUCT or index/match question)
    By justinhampton81 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-05-2013, 11:56 PM
  3. [SOLVED] VLOOKUP (or Index/Match) across four columns -- and returning multiple matches
    By rcasey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-11-2013, 05:20 PM
  4. [SOLVED] vlookup or index-match for multiple columns
    By nmprodan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2013, 10:48 PM
  5. VLookup / Match / Index question
    By mattyb in forum Excel General
    Replies: 2
    Last Post: 03-16-2009, 02:49 PM

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