Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookups, IF, Match and Index query

    Hi all,

    I'm having a huge issue with a spreadsheet the I'm trying to write and just when I think I've got the answer, POW, it doesn't work. Hopefully someone can assist. I'll attach a test spreadsheet below.

    What I'm trying to do, is retrieve staff leave (on one tab) based on the logon ID they enter (on another tab).

    The staff member will enter there logon in cell D10 on "sheet1". The leave data is saved on "sheet4"

    Basically, I want to retrieve the max value (2013, 2014, 2015 or "N/A" if never) from column R based on the staff logon on Sheet 1 D10 and the Settlement Period (SP) and week in Sheet 2. I've tried to simplify the SP and Week a little by allocating a number for each SP and week combination. For example SP1 weeks 1 to 4 a number of 11 through 14, SP4 weeks 1 to 4 a number of 21 through 24 upto SP13 (Column Q). This way, the formula should only require a VLOOKUP based on the logon, an IF based on the value in column Q and a MAX based on the year. It seems simply enough to me, but obviously not.

    I have gone down the path of attempting INDEX/MATCH combinations but am a little out of my league there.

    For those wanting to test formulas, please try in cell W10 of sheet 1 as there is data for that week associated with the Logon (AGS) and corresponding leave for that period for a previous year.

    Thanks

    Guyan
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I might be overlooking something in your description, but I don't see any data in W10 on Sheet1.

    It's unclear to me what you're trying to insert in Sheet4 (and where) with a VLOOKUP.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    It's unclear to me too.
    Let's have another sample file.

    zeddy

  4. #4
    New Lounger
    Join Date
    Oct 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    It's unclear to me too.
    Let's have another sample file.

    zeddy
    Hi,

    I'm not trying to enter any data into Sheet 4, I'm trying to extract the max data from column R in Sheet 4, based on the Staff logon (AGS) in Cell D10 of Sheet 1 (AGS Data is stored in Column B of Sheet 4 for Vlookup) and the number in column Q in Sheet 4 (SP and week ie SP1 Week4 = 14) into cell W10 of Sheet 1.. If there is no data that reflects the above, I want cell D10 of Sheet1 to read as Week 4.

    So effectively, in cell W10 of sheet 1, I need a formula that reads similar to =MAX($R$R,IF(Sheet4!$Q:$Q=14,Vlookup(Sheet1!$D10,S heet4!$B:$R,18,0)),"week4")..

    I didn't realise that I had attached an older test file as I've been working on this a while now... I've coloured Column R Sheet 4 Yellow as this is the data I need to extract, Cell D10 Sheet1 and Column Q Sheet 4 Green as these are the variables and Cell W10 Sheet 1 Red as this is where I want the formula.

    Hopefully this clears it up a bit.


    I'll add the updated version (titled the same "Leave 2.xlsm"
    Attached Files Attached Files
    Last edited by Guyan Kognito; 2014-10-21 at 18:47.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Frankly, I'm still lost on what it is you're trying to select. On Sheet1, there is no W10 cell. There is a merge of V10:X10 which maybe you think is W10 because the merge incorporates that cell reference. ??

  6. #6
    New Lounger
    Join Date
    Oct 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    Frankly, I'm still lost on what it is you're trying to select. On Sheet1, there is no W10 cell. There is a merge of V10:X10 which maybe you think is W10 because the merge incorporates that cell reference. ??
    Hi Kweaver,

    So sorry for the confusion... I've changed this spreadsheet so many times trying to get this damn thing to work... The cell that I require the formula in for this example is W13. It's highlighted Red.

  7. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    This is the way a max(if formula works. To make it easier I have defined names for the columns (see formulas>name manager) in the attached. And, this formula is an array formula that must be entered using Ctrl+shift+enter (cse)

    =MAX(IF(ags=D10,IF(sps=A13,years)))
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Oct 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dguillett@gmail.com View Post
    This is the way a max(if formula works. To make it easier I have defined names for the columns (see formulas>name manager) in the attached. And, this formula is an array formula that must be entered using Ctrl+shift+enter (cse)

    =MAX(IF(ags=D10,IF(sps=A13,years)))
    Hi,

    Thanks for the help with the above formula, however it didn't solve the issue unfortunately. I'll keep trying though... Thanks again

  9. #9
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Please tell us WHY it didn't work and what more you expect it to do????? A larger sample might be good or the ACTUAL file.

  10. #10
    New Lounger
    Join Date
    Oct 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The formula =MAX(IF(Sheet4!A:A=Sheet1!B13,IF(Sheet4!Q:Q=14,She et4!R:R))) written as an array of course, is presenting a "0" response whereas there are 2 separate years in Sheet 4 Column that are associated with the AGS and SP1/WEEK4. With 2013 being the most recent year, the result should be 2013.

    And a larger sample wouldn't help. If we can't get the formula to work in 1 cell which the data we have, how will adding more data help?? The AGS (82556853) has enough data for SP1 WEEK4 to bring up a relevant response.

  11. #11
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    IF you change the format of your v10:z10 from MERGE to CENTER ACROSS and use the formula, AS WRITTEN, and then apply the CSE it works just fine for the parameters given........
    See attached.
    Attached Files Attached Files

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    As a different approach, it is possible to use Excel's DMAX function to return the maximum value from a specified column, in a specified data block, that meets specified conditions.

    In the attached file I named the source data range on [Sheet4] as dataBlock i.e. assigned this range name to cells [A1:R9]

    On the sheet [Sheet1], I defined a named range critBlock referring to cells [AH5:AJ6] as the 'specified conditions'. Note that your data in column [K] on [Sheet4] has values such as Week4 rather than 'Week 4' i.e. no spaces.

    The formula to return the maximum value in the specified data block on [Sheet4] from the 18th column (i.e. column [R] on [Sheet4] ) that meets the specified conditions defined in named range critBlock is then

    =DMAX(dataBlock,18,critBlock)

    zeddy
    Attached Files Attached Files

Posting Permissions

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