Results 1 to 4 of 4

Thread: Lookup/Match Array Formula over multiple sheets

  1. #1

    Lookup/Match Array Formula over multiple sheets

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need a formula to search column B on multiple sheets for a name.
    For every match I need it to return the values/names in columns C, D, & E of that row. I have a formula now that give me what I need from 1 sheet but I can't figure out how to do this over multiple sheets.
    {=IF(ROWS(C$6:C8)<=$A$2,INDEX(LASSEN!E$2:E$1041,SMALL(IF(LASSEN!$B$2:$B$1041=$A$1, ROW(LASSEN!$B$2:$B$1041)-ROW(LASSEN!D$2)+1),ROWS(C$6:C8))),"")}
    I have spent countless hours researching this and have tried tons of array lookup formulas with no luck. Any help would be greatly appreciated.

  2. #2

    Index Array That References Multiple Sheets

    Hello. If I understand what you are trying to do, then I might be able to help you to some extent. If I am on the same page as you, then I am trying to do the same thing as you. I hope that I am able to assist you.
    How many pages do you have in your document? I could only get this formula to work for up to three pages. Unfortunately, I cannot figure out how to make it get data from more than three pages. Everytime I try to expand the code for List4, it says I have too many arguments.

    Because it won't let me upload my file here, I have uploaded my sample excel file elsewhere:

  3. #3
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    Lookup names and their values/text across multiple sheets (9 tabs in this example)
    No formulas, no helper columns, no macro needs to be written.
    Sam: Could not open your link on PC.

  4. #4
    I found out that there is a limit to nested ifs. I found a way around the limitation by using defined names to replace the coding. This does work, but instead of getting an error that says "too many arguments" I am getting a #NUM error in my cells for List4 through List6. I have also re-uploaded my excel file. Hopefully, the link will work this time.

Tags for this Thread

Posting Permissions

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