+ Reply to Thread
Results 1 to 8 of 8

Multiple lookup query, AND/IF multiple sheets

  1. #1
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Multiple lookup query, AND/IF multiple sheets

    Hi
    I’m trying to provide an outcome based on data entered in Cell H1 of the “01 Clients Units by year” sheet that then references to individual client sheets providing the result outcome in the appropriate year.
    The formula should identify both the year and unit in the client sheet, and if both the unit and year are populated in the sheet, then the result is duplicated in the appropriate year of the “ 01 Clients Units by year” sheet.
    The text in Cell H1 is changeable, so at any given time the formula will always reference to H1, and provide results according to the unit code typed in H1.
    The desired outcome is represented in the “01 Clients Units by year” sheet.

    Formula added, issues with Vlookup in Row C, works with hard key full name, but not with Vlookup in the Full Name Column C.

    I have reloaded the test file with the formula supplied and the Vlookup added in column C.
    Attached Files Attached Files
    Last edited by Christopherdj; 03-29-2012 at 08:46 PM. Reason: vlookup issue - formua not working.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Multiple lookup query, AND/IF multiple sheets

    Could you please attach sheet?
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Multiple lookup query, AND/IF multiple sheets

    Hi there, thanks for attaching the sheet, give this formula a shot and see if that is the results you are looking for

    =IFERROR(INDEX(INDIRECT($C3&"!F1:F70"),IF(SUMPRODUCT(--(INDIRECT($C3&"!$C2:$C70")=$H$1)*--(INDIRECT($C3&"!$G2:$G70")=H$2)*ROW($G2:$G70))=0,NA(),SUMPRODUCT(--(INDIRECT($C3&"!$C2:$C70")=$H$1)*--(INDIRECT($C3&"!$G2:$G70")=H$2)*ROW($G2:$G70)))),"")

    Starts in H3 fill rignt and down.

  4. #4
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Multiple lookup query, AND/IF multiple sheets

    Works fine with first line, but distorts results in lines 4 and 5. By filling right and down, the result for Client Brown shows NC in H5, but should be CC at H5.
    I think it is not holding the appropriate references. I have since added the $ to all row references and it works fine.

    =IFERROR(INDEX(INDIRECT($C3&"!F1:F70"),IF(SUMPRODUCT(--(INDIRECT($C3&"!$C$2:$C$70")=$H$1)*--(INDIRECT($C3&"!$G$2:$G$70")=H$2)*ROW($G$2:$G$70))=0,NA(),SUMPRODUCT(--(INDIRECT($C3&"!$C$2:$C$70")=$H$1)*--(INDIRECT($C3&"!$G$2:$G$70")=H$2)*ROW($G$2:$G$70)))),"")
    It now works fine - thank you so much.
    Last edited by Christopherdj; 03-27-2012 at 11:55 PM.

  5. #5
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Multiple lookup query, AND/IF multiple sheets

    it was the references in the row function, i forgot to $ the row numbers, try this

    =IFERROR(INDEX(INDIRECT($C3&"!F1:F70"),IF(SUMPRODUCT(--(INDIRECT($C3&"!$C2:$C70")=$H$1)*--(INDIRECT($C3&"!$G2:$G70")=H$2)*ROW($G$2:$G$70))=0,NA(),SUMPRODUCT(--(INDIRECT($C3&"!$C2:$C70")=$H$1)*--(INDIRECT($C3&"!$G2:$G70")=H$2)*ROW($G$2:$G$70)))),"")

  6. #6
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Multiple lookup query, AND/IF multiple sheets

    It looks like there is a space in the sheet name now, give this a shot, it will protect against that

    =IFERROR(INDEX(INDIRECT("'"&$C3&"'!F1:F70"),IF(SUMPRODUCT(--(INDIRECT("'"&$C3&"'!$C$2:$C$70")=$H$1)*--(INDIRECT("'"&$C3&"'!$G$2:$G$70")=H$2)*ROW($G$2:$G$70))=0,NA(),SUMPRODUCT(--(INDIRECT("'"&$C3&"'!$C$2:$C$70")=$H$1)*--(INDIRECT("'"&$C3&"'!$G$2:$G$70")=H$2)*ROW($G$2:$G$70)))),"")

  7. #7
    Forum Contributor
    Join Date
    10-13-2011
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    329

    Re: Multiple lookup query, AND/IF multiple sheets

    Problem Solved - apologies, I should have had the correct Sheet Names in the test file. I was unaware it would create an issue with the formula.

  8. #8
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Multiple lookup query, AND/IF multiple sheets

    no problem, glad i could help you out.

    best of luck on the project

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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