+ Reply to Thread
Results 1 to 8 of 8

INDEX MATCH Functions

  1. #1
    Registered User
    Join Date
    11-02-2016
    Location
    London, UK
    MS-Off Ver
    2013
    Posts
    5

    INDEX MATCH Functions

    Hello All,

    First post here. I had a job interview which involved the attached Excel test. My knowledge is sketchy at best, so needless to say I failed the test.

    I would be extremely grateful if you could take a look at the Q2 tab. The question asks for a different method for each.

    Can you offer me any pointers? You can probably tell that this has been bugging me a lot!

    Thanks,

    Patrick
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: INDEX MATCH Functions

    I think if I were asking people to do this case study in an interview I would want to see a pivot table for each of the answers (and really a pivot table IS the easiest way to answer these questions aside from just filtering to answer).

    Seeing as they locked the workbook, I would guess they want to see you just filter your way to success on this silly question. Unless they want to see your capacity with Array formulas...
    Last edited by mikeTRON; 11-02-2016 at 04:30 PM.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    11-02-2016
    Location
    London, UK
    MS-Off Ver
    2013
    Posts
    5

    Re: INDEX MATCH Functions

    I believe that they were looking to test my competence with Array formulas. Would you have any suggestions?

    I tried a pivot table to no avail for the reasons you highlight.

    Thanks in advance.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,232

    Re: INDEX MATCH Functions

    I believe that they were looking to test my competence with Array formulas. Would you have any suggestions?
    What did you manage to do with this part
    a. ……. top 10 holdings by index weight
    while referencing the
    as of 31/12/2015.
    part?

    Had you tried any helper columns?
    Dave

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: INDEX MATCH Functions

    Quote Originally Posted by paddyanglais91 View Post
    I believe that they were looking to test my competence with Array formulas. Would you have any suggestions?
    I tried a pivot table to no avail for the reasons you highlight.
    Thanks in advance.
    I would tell them it would be more efficient to use a pivot table and or VBA than array formulas. I try not to use arrays unless it IS the most simple solution and you wont have many in a workbook. Then I would show them how to use a pivot table to the right of the data

  6. #6
    Registered User
    Join Date
    11-02-2016
    Location
    London, UK
    MS-Off Ver
    2013
    Posts
    5

    Re: INDEX MATCH Functions

    Quote Originally Posted by mikeTRON View Post
    I would tell them it would be more efficient to use a pivot table and or VBA than array formulas. I try not to use arrays unless it IS the most simple solution and you wont have many in a workbook. Then I would show them how to use a pivot table to the right of the data
    That has put my mind at ease somewhat. Never in my life have I come up against something as confusing as this!

    I remember that I recommended using pivot tables when I attempted it. I'm not going insane

  7. #7
    Registered User
    Join Date
    11-02-2016
    Location
    London, UK
    MS-Off Ver
    2013
    Posts
    5

    Re: INDEX MATCH Functions

    Quote Originally Posted by FlameRetired View Post
    What did you manage to do with this part while referencing the part?

    Had you tried any helper columns?
    I tried this but it was in vain - I think I will put this down to bad experience!

    What do you mean by helper columns?

  8. #8
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: INDEX MATCH Functions

    Quote Originally Posted by paddyanglais91 View Post
    That has put my mind at ease somewhat. Never in my life have I come up against something as confusing as this!

    I remember that I recommended using pivot tables when I attempted it. I'm not going insane
    Whenever I administered these excel based case studies in an interview I was always most interested in HOW the person went about solving the problem, or even identifying it instead of just the correct answer. Most of the time we wanted to know that they knew how to manipulate a large dataset and knew how to check their answers. Some people could solve some quetsions with some slick formulas which is great, but I always preferred them to make a VERY simple pivot table and pivot to success.

    Anyway, you CAN ask them for a followup and see if they can walk you through how they would solve that particular question or one like it so you are familiar in the future. I doubt they will, but you never know. I also doubt anyone has ever asked them for further advice AFTER an interview... which might help you the NEXT time they have an open role.

    Networking > Excel Skills.

+ 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. Help with index and match functions
    By romasasss in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2013, 02:47 AM
  2. Index and match functions
    By Firecrest in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 07-05-2012, 09:39 AM
  3. [SOLVED] Using Index and Match functions.
    By jnweiss in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2012, 04:20 PM
  4. Help Please INDEX & MATCH functions
    By christine.golledge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2012, 06:47 PM
  5. Using Index & Match Functions
    By DanSherwood in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-22-2008, 09:48 PM
  6. Index And Match Functions
    By Ansonicus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2008, 02:20 PM
  7. INDEX/MATCH functions
    By mrmunka in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-10-2007, 11:08 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