+ Reply to Thread
Results 1 to 12 of 12

Multiple HLookup with OR giving false positive readings

  1. #1
    Registered User
    Join Date
    06-19-2018
    Location
    Midlands
    MS-Off Ver
    2016
    Posts
    9

    Multiple HLookup with OR giving false positive readings

    HI there,

    I am creating a calendar whereby the rule is the lead name cannot be on holiday at the same time as the subordinate names (see Rules tab)
    In tab '01.04' I have a formula that is working for everyone except the last one highlighted in yellow. In addition considering I only use 2 rows in my formula, how is the pink highlighted cell giving 3 answers?
    Please try: tick the box for Column J Row 2 = should put a H in his cell and a R in column E & I - but doesn't.
    tick the box for Column I Row 2 = should put a H in his cell and a R in column J, F & E - which it does BUT I'm only referring to 'Rules' rows 2 and 3? So how is it picking up the 3 names correctly?

    I'm very puzzled!

    As a side note: it also won't clear the cells if you tick and untick unless you 'save' the file :S

    Anybody that can help is a legend! :D
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Multiple HLookup with OR giving false positive readings

    Please try at B5:J6

    =IF(OR(B$2,B3),"H",IF(SUMPRODUCT(N(($B$2:$J$2+$B3:$J3)*COLUMN($B3:$J3)=MATCH(INDEX(Rules!$D$2:$L$6,,MATCH(B$1,Rules!$D$1:$L$1,))&"",INDEX($A$1:$L$1&"",),))),"R",""))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-19-2018
    Location
    Midlands
    MS-Off Ver
    2016
    Posts
    9

    Re: Multiple HLookup with OR giving false positive readings

    Small issue, if I add 'E Green' underneath 'L Whitby' as the lead - when I check Annual leave for E Green it shows that L Whitby as R, but it shouldn't do, it should only be R Shorthose.
    It seems to be putting a R against any name that has E Green in their list, not the ones that are in E Green's list?
    Last edited by amerandstarbuck; 05-22-2019 at 08:02 AM.

  4. #4
    Registered User
    Join Date
    06-19-2018
    Location
    Midlands
    MS-Off Ver
    2016
    Posts
    9

    Re: Multiple HLookup with OR giving false positive readings

    Can anyone also advise how to transpose the formula from tab '01.04' should I want it presented as in tab '01 Apr'
    I have tried but I'm honestly baffled
    Attached Files Attached Files

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Multiple HLookup with OR giving false positive readings

    Name in row 1 for both sheets need to sort in the same order
    Please try 01.04
    B6
    =IF(OR(B$2,B3),"H",IF(SUMPRODUCT(N(MATCH(Rules!$D$2:$L$6&"",$B$1:$L$1&"",)*(($B$2:$J$2)+($B3:$J3)>0)=COLUMNS($B1:B1))),"R",""))

    01 Apr
    E2
    =IF(OR($B2,$C2),"H",IF(OR(TRANSPOSE(MATCH(Rules!$B$2:$K$6&"",$A$2:$A$99&"",))*(($B$2:$B$11)+($C$2:$C$11)>0)=ROWS($A$2:$A2)),"R",""))
    Press Ctrl+Shift+Enter
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-19-2018
    Location
    Midlands
    MS-Off Ver
    2016
    Posts
    9

    Re: Multiple HLookup with OR giving false positive readings

    You are a genius Bo_Ry - thank you so so much, really truly appreciated

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Multiple HLookup with OR giving false positive readings

    Happy to help.

  8. #8
    Registered User
    Join Date
    06-19-2018
    Location
    Midlands
    MS-Off Ver
    2016
    Posts
    9

    Re: Multiple HLookup with OR giving false positive readings

    Bo-Ry...I thought it would be easier to manipulate the formula for the half day bit....but it doesn't work
    I'd really like to know the science behind your formula but if you can't explain can you just let me know how to change it so that when column D is true the second half matches like the first half?
    Please and thankyou ever so much
    Attached Files Attached Files

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Multiple HLookup with OR giving false positive readings

    The formula already in N2 , you need to press Ctrl+Shift+Enter { } will wrap the formula if you do correctly.

    Explanation is really complicate.

    =IF(OR(TRANSPOSE(MATCH(Rules!$B$2:$K$6&"",$A$2:$A$99&"",))*(($B$2:$B$11)+($D$2:$D$11)>0)=ROWS($A$2:$A2)),"R","")

    =MATCH(Rules!$B$2:$K$6&"",$A$2:$A$99&"",)
    Rules!$B$2 = "E Woodward"
    =MATCH("E Woodward",$A$2:$A$99&"",) =3
    Rules!$C$2 = "R Shorthose"
    =MATCH("R Shorthose",$A$2:$A$99&"",) =9
    and so on
    the &"" add for avoide error for blank cell and show 11 instead of #N/A

    You will get these array result for Rules!$B$2:$K$3
    ={
    3,9,1,4,5,1,11,5,2,11;
    6,11,6,8,8,3,11,4,8,11}
    , Comma for next in column ; semi colon for next row
    a. then =TRANSPOSE(MATCH(Rules!$B$2:$K$3&"",$A$2:$A$99&"",))
    give ={3,6;9,11;1,6;4,8;5,8;1,3;11,11;5,4;2,8;11,11}


    b. =($B$2:$B$11)+($D$2:$D$11)>0)
    if B2 or D2 are checked.
    this will give {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
    a*b
    =
    ={3,6;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0}

    3 for E Woodward
    6 for L Whitby

    row 2 =ROWS($A$2:$A2) =1
    row 4 =ROWS($A$2:$A4) =3 = E Woodward
    row 7 =ROWS($A$2:$A7) =6 = L Whitby

    =IF(OR(a*b = rows(),"R","")

  10. #10
    Registered User
    Join Date
    06-19-2018
    Location
    Midlands
    MS-Off Ver
    2016
    Posts
    9

    Re: Multiple HLookup with OR giving false positive readings

    Quote Originally Posted by Bo_Ry View Post
    The formula already in N2 , you need to press Ctrl+Shift+Enter { } will wrap the formula if you do correctly.

    Explanation is really complicate.
    - gonna have a cup of tea and try to digest - thank you ever so much for all your help, you have been amazing

  11. #11
    Registered User
    Join Date
    06-19-2018
    Location
    Midlands
    MS-Off Ver
    2016
    Posts
    9

    Re: Multiple HLookup with OR giving false positive readings

    Hey Bo-Ry
    Attached is new layout, wanted to just put a 'Y' instead of the check boxes.
    Thanks ever so much
    Attached Files Attached Files

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Multiple HLookup with OR giving false positive readings

    MATCH(Rules!$B$2:$K$6&"",$A$2:$A$99&"",)
    All Name from Rules!$B$2:$K$6 must present in '01 Apr A2:A11, otherwise Match return error and won't calculate

    Also B:K 9 columns transpose to 9 rows must equal to B2:B11 9 rows

    E2
    =IFERROR(IF(OR($B2="y",$C2="y"),"H",IF(OR(TRANSPOSE(MATCH(Rules!$B$2:$K$6&"",$A$2:$A$99&"",))*(($B$2:$B$11="y")+($C$2:$C$11="y")>0)=ROWS($A$2:$A2)),"R","")),"")

    Press Ctrl+Shift+Enter
    Attached Files Attached Files

+ 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. [SOLVED] CHECKSUM Formula in three months giving false reading in one of them
    By Ochimus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-04-2019, 11:18 PM
  2. [SOLVED] Get Rid of the False Positive Results of the Code.
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-11-2013, 11:01 AM
  3. Replies: 2
    Last Post: 10-30-2013, 10:49 AM
  4. My HLOOKUP is not giving the answer I expect
    By SparkleHedgehog in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-03-2013, 07:41 AM
  5. HLOOKUP formula keeps giving me a value error
    By dandavis1 in forum Excel General
    Replies: 5
    Last Post: 08-10-2011, 07:46 AM
  6. =If formula giving a false return
    By Jogier505 in forum Excel General
    Replies: 7
    Last Post: 03-04-2011, 05:20 PM
  7. IF function returns false positive
    By AJMaine in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-07-2009, 03:09 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