Vlookup across Multiple Sheets with multiple results

    Re: Vlookup across Multiple Sheets with multiple results


    12 results on the Function sheet, 9 on the new Sheet4 (with the new range) and 12 on the old Sheet4.

    Re: Vlookup across Multiple Sheets with multiple results


    Code
    =IFERROR(INDIRECT(CONCATENATE("'",Function!$A$8,"'!A",SMALL(Function!$F$2:$F$1500,ROW()-1))),"")

    Re: Vlookup across Multiple Sheets with multiple results


    Select you first cell on sheet4 and highlight in the formula bar
    CONCATENATE("'",Function!$A$8,"'!A",SMALL(Function!$F$2:$F$1500,ROW()-1))
    and press F9. (press Ctrl+Z to return it back to the formula)
    When you press F9, you should see something like 'Sheet1'!A14
    If you selected the first cell, you should see the number after the column letter (e.g. 14) should be the first number of the function sheet on the column that prints out the row numbers of matches.


    Is this true?
    Also $F$2:$F$1500 might have to be edited to fit you column of row numbers.

    Re: Vlookup across Multiple Sheets with multiple results


    I did some testing. In one formula, there has to be something that tells it to start searching on A2. I verified that because on the new Sheet4, I pasted the formula and a result appeared.

    Re: Vlookup across Multiple Sheets with multiple results


    On Sheet4 I did. They start on A5:E5. The "Find' box is now B2. Every time I search, I noticed that the first 3 finds are not shown, but display in the Function tab.

    Re: Vlookup across Multiple Sheets with multiple results


    On sheet4 change the formula from
    ROW()-1
    to
    ROW()-4


    EXPLANATION: ROW() gets the row number of the cell it is in. Since you started in A5 the row number is 5 minus 1 is 4. So that 4 is passed to the small function saying find the 4th smallest number in it's range. So it would always skip over the first 3 matches

    Re: Vlookup across Multiple Sheets with multiple results


    I'm feeling a little creative/curious.


    Do you know of a way to offer a secondary search (placed underneath the primary on sheet4) that would filter through the existing results found in the 'D' column?

    Re: Vlookup across Multiple Sheets with multiple results


    Yes. Originally, I just wanted to apply a filter, but that didn't fly so well. Can a search be applied to the searched options? It needs to target the D column, but I am unsure how to even start.

    Re: Vlookup across Multiple Sheets with multiple results


    Ok, explain to me what the search will do? Will this search be optional? How or what are we searching for? What is being searched through?

    Re: Vlookup across Multiple Sheets with multiple results


    The D column contains specifics that pertain to the search results. Column A holds the search results, B and C arent related to this request, but everyone wants to filter the things that are in column D.


    The secondary search would be completely optional and would contain words or specific abbreviations, not special characters or numbers or partial words.

    Re: Vlookup across Multiple Sheets with multiple results


    Where will this value be place to do the secondary search? How will we determine to do the second search or not? Are we searching for partial matches again or full matches?

    Re: Vlookup across Multiple Sheets with multiple results


    The additional search 'box' would be in B3. The secondary search would be optional and only used if the person wants to utilize it. Otherwise, it will be blank until something is inserted.


    I'm thinking that complete words would be best for this secondary function.

    Re: Vlookup across Multiple Sheets with multiple results


    Wow, that was fast!


    As far as modification goes, what exactly do I need to change? Is it still a complete drag n' drop as before?


    Thank you so much.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login