Vlookup across Multiple Sheets with multiple results

    Re: Vlookup across Multiple Sheets with multiple results


    It works nicely, and quickly. Do you think it will be quick with up to 3,000 lines?


    Also, is it still possible to show no results when G2 is empty?

    Re: Vlookup across Multiple Sheets with multiple results


    I think it will still be very fast.


    Yes, in the function sheet, change the formula underneath Search2-4. You will be looking for Len($A$2). Change it to $A$3 for Search2, $A$4 for Search3, and $A$5 for Search4

    Re: Vlookup across Multiple Sheets with multiple results


    and adding columns, how would I go about doing that?


    Thank you for all your help with this!

    Re: Vlookup across Multiple Sheets with multiple results


    This is the formula on sheet4. Simple replace COL with a column name (e.g. D, F, AA, BA, DB)
    =IFERROR(INDIRECT(CONCATENATE("'",Function!$A$8,"'!COL",SMALL(Function!$F$2:$F$500,ROW()-1))),"")

    Re: Vlookup across Multiple Sheets with multiple results


    Brainfart on my end, but does this go into a new cell on Sheet4, or modified an existing formula?

    Re: Vlookup across Multiple Sheets with multiple results


    Hey, it's me again.


    On the function tab, it is finding matches, but past the 600th line, it doesn't display on Sheet4. I have it set to read up to 1000 lines, but have no idea why this isn't transfering to Sheet4. Any thoughts?


    Nevermind! Just had to increase the value on the formula in Sheet4.

    Re: Vlookup across Multiple Sheets with multiple results


    Ok, I did find one issue.


    Code
    =IF(AND(COUNTIF(INDIRECT(CONCATENATE("'",$A$8,"'!",ADDRESS(ROW(),1))),"=*" & $A$2 & "*")=1,LEN($A$2)>0),1,0)


    That's on line 804. When the search field is blank, it pulls from 804.


    Any thoughts on that?

    Re: Vlookup across Multiple Sheets with multiple results


    I tried to recreate your error with no luck. What do you mean it pulls from 804? It pulls line 804?

    Re: Vlookup across Multiple Sheets with multiple results


    I narrowed it down and realized that the number 0 does not cooperate. I just changed it to an uppercase O and settled on that.


    If I were to colorcode the list that information is pulled from, is there a chance the color could also travel with the information for the results?

    Re: Vlookup across Multiple Sheets with multiple results


    Well, shoot... I guess that's what happens when so many limitations are placed on an Excel project!

    Re: Vlookup across Multiple Sheets with multiple results


    One final question (hopefully) and I think I have this formula madhouse understood from top to bottom.


    I modified the 'find' field from G2 to B2 and changed the formulas to match. However, with the old 'Sheet4', the results started on A2, but on the new 'Sheet4', the results start on A5, but some results are not included. From 2 - 4, it doesn't show.


    Is there something i'm missing in a formula? Some type of range?

    Re: Vlookup across Multiple Sheets with multiple results


    Then I would have to presume that it's something on sheet4. Give this a shot. On sheet4 put somewhere =COUNTA(RANGE) RANGE being the range of data that shows up. You should get a number that represents how many items there are. Go to the function sheet. Where there is a column of row numbers and falses at the top you should see a number. That is the count of how many matches there are. So in the grand scheme of things, the numbers should be the same. What two numbers do you get?

Participate now!

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

Register Yourself Login