Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I am trying to write a if formula where there are 2 conditions that must be met. I am attaching a sample worsheet to demonstrate. The reference schedule has all basic information. The query schedule is missing the HRS. and this is where the if statement should be. The if statement should look to the REFERENCE SCHEDULE and if the ID agrees and the AMOUNT agrees then the REFERENCE SCHEDULE's HRS. should be posted in the approriate QUERY SCHEDULE's HRS cell.

    Thanks

    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Not sure what is the desired result.
    Attached is a sample Table of Total Hours and Total Amounts by Dept and by ID.

    Please note that Reference Sch. in Cells B22 and B27 contain two different versions of Dept ADE. B22 has ADE and B27 has ADE (space).
    Attached Files Attached Files

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In H4 enter:
    =LOOKUP(9.99E+305,CHOOSE({1,2},0,LOOKUP(2,1/(($A$4:$A$31=F4)*($B$4:$B$31=G4)),$C$4:$C$31)))
    and copy down, if I understood correctly.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Are you saying an IF formula wth an AND statement will not work?

    I am hoping the results will be an HRS amount which fulfills both the ID & the AMOUNT criteria. Is this possible to achieve? If so, what is the formula.

    Thank you.


  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Still trying to figure out what is wanted for HRS on QUERY SCHEDULE.
    Please supply the correct HRS that should show on QUERY SCHEDULE?

    QUERY SCHEDULE contains AMT's that don't exist on REFERENCE SCHEDULE
    For example -
    these AMT's from QUERY SCHEDULE do not exist on REFERENCE SCHEDULE .....
    8,300.00 5,944.00 1,233.00 1,200.00

    Please be clearer on how HRS should be calculated.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Sorry for not being clear enough in my text.

    The objective is to have the QUERY SCHEDULE record the hours from the REFERENCE SCHEDULE if there is a match with the ID & the AMOUNT on the REFERENCE SCHEDULE.

    If the REFERENCE SCHEDULE matches the ID but not the AMOUNT or the AMOUNT but not the ID then no hours will be recorded in the QUERY SCHEDULE and those cells shall remain blank.

    I am attaching the worksheet with the HRS recorded in the QUERY


  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Ooops pulled trigger too fast!

    Sorry for not being clear enough in my text.

    The objective is to have the QUERY SCHEDULE record the hours from the REFERENCE SCHEDULE if there is a match with the ID & the AMOUNT on the REFERENCE SCHEDULE.

    If the REFERENCE SCHEDULE matches the ID but not the AMOUNT or the AMOUNT but not the ID then no hours will be recorded in the QUERY SCHEDULE and those cells shall remain blank.

    I am attaching the worksheet with the HRS recorded in the QUERY Schedule

    Again, sorry for not being concise
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi - I am still confused here. The first one I checked does not ring a bell.
    How does Cell H5 become 16? What matches were used to get a 16 in Cell H5?

    Thanks

  9. #9
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    The Hrs. cells in the Query Schedule will house the formula. The formula will look to the Reference Schedule and see if there is an exact match to both the ID and the AMOUNT in the same row. If there is then the Hrs. are to be recorded in the Query Schedule. there may not be a match for each set of ID and AMOUNT in the Query Schedule from the Reference schedule

    So the formula should translate to " If in the Query Schedule the ID & Amount in a row agrees to the ID & Amount in the Reference Schedule then record the HRS from the agreed record in the Reference Schedule in the HRS cell in the Query Schedule.


    I fixed the w/s. and attached

    Thanks
    Attached Files Attached Files

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Did you want "" or 0 returned where there is no match?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    A blank space would be good.

    Thank you.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In that case:
    =IF(ISNA(LOOKUP(2,1/(($A$4:$A$31=F4)*($D$4:$D$31=I4)),$C$4:$C$31)),"", LOOKUP(2,1/(($A$4:$A$31=F4)*($D$4:$D$31=I4)),$C$4:$C$31))
    in H4 and copy down.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    In that case:
    =IF(ISNA(LOOKUP(2,1/(($A$4:$A$31=F4)*($D$4:$D$31=I4)),$C$4:$C$31)),"", LOOKUP(2,1/(($A$4:$A$31=F4)*($D$4:$D$31=I4)),$C$4:$C$31))
    in H4 and copy down.

    Thank you very much.

    What was the significance of the "=LOOKUP(9.99E+305,CHOOSE({1,2}" in the first formula?

  14. #14
    New Lounger
    Join Date
    Dec 2009
    Location
    England
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've adopted a slightly (or very) different approach. I envisaged a problem in the accuracy of your method in that rows #5 and #6 contained the same ID, AMT, and HRS, but a different DEPT.

    CONCATENATE the ID etc. =CONCATENATE(A3,B3,D3)
    In H4 and below =OFFSET($C$2,MATCH(CONCATENATE(F3,G3,I3),$E$3:$E$2 9,0),)

    It is of course very important to ensure that things like the errant space in cell G16 are corrected.

    [attachment=88970:suggestion.xls]
    Attached Files Attached Files

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by MNN View Post
    What was the significance of the "=LOOKUP(9.99E+305,CHOOSE({1,2}" in the first formula?
    It was to return 0 if there was no match, instead of a #N/A error. That approach would not work to return "" though, so I altered it to use ISNA() instead.
    PS If you wanted to add a department check into the formula too, that's easy enough.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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