+ Reply to Thread
Results 1 to 3 of 3

Need help nesting an index/match function within a Vlookup function.

  1. #1
    Registered User
    Join Date
    10-16-2013
    Location
    Queensland, Australia
    MS-Off Ver
    MS365, Version 2202
    Posts
    73

    Need help nesting an index/match function within a Vlookup function.

    Hello again Excel Guru's!

    let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner. Sorry guys!

    You guys have been helping me a lot with this formula, and I've come to ask for another peice of help. This is the formula as it stands:

    Please Login or Register  to view this content.
    In plain english, this formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.

    I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:

    This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
    Match: Lookup value = $E3
    Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
    match type = 0

    This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered

    Match: Lookup value = $A$4
    Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
    match type = 0

    Index: array = $BA$434:$DN$452

    So I think my final function is

    Please Login or Register  to view this content.
    But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.

    Please help guys!
    Last edited by Christopher135; 12-03-2013 at 08:50 PM. Reason: sample workbook apologies

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Need help nesting an index/match function within a Vlookup function.

    It would be easier to understand your request if you attached a workbook.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    10-16-2013
    Location
    Queensland, Australia
    MS-Off Ver
    MS365, Version 2202
    Posts
    73

    Re: Need help nesting an index/match function within a Vlookup function.

    Quote Originally Posted by RobertMika View Post
    It would be easier to understand your request if you attached a workbook.
    I'm aware, however as I mentioned, the spreadsheet this formula pulls data from is immense, and chock-full of sensitive data that I can't post publicly. To scrub it of sensitive data would absolutely guaruntee to break it, since so much of the spreadsheet is dependent/created from the sensitive data.

+ 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. Function VLookup but need Index-Match
    By thorrrr in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 10-31-2013, 05:52 AM
  2. [SOLVED] Need assistance on nesting an indirect function in a index / match formula
    By ghosters in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2012, 06:00 AM
  3. VLOOKUP / INDEX / MATCH Function??
    By liverpoolphil in forum Excel General
    Replies: 2
    Last Post: 10-08-2009, 09:30 AM
  4. Function Vlookup, Match or Index?
    By Patrick Young in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-03-2005, 10:05 PM
  5. Function Vlookup, Match or Index?
    By Patrick Young in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2005, 07:05 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