Results 1 to 12 of 12

20141020, 23:46 #1
 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

20141021, 02:10 #2
 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.

20141021, 11:01 #3
 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

20141021, 18:39 #4
 Join Date
 Oct 2014
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts
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"Last edited by Guyan Kognito; 20141021 at 18:47.

20141022, 00:18 #5
 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. ??

20141022, 00:47 #6
 Join Date
 Oct 2014
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts

20141022, 08:53 #7
 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)))

20141022, 17:08 #8
 Join Date
 Oct 2014
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts

20141022, 17:13 #9
 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.

20141022, 17:27 #10
 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.

20141022, 18:41 #11
 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.

20141024, 07:24 #12
 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