Vlookup across Multiple Sheets with multiple results

    Re: Vlookup across Multiple Sheets with multiple results


    Well I did it, but it's as I feared. We are back to some noticeable lag.

    Re: Vlookup across Multiple Sheets with multiple results


    Hmm, lag is something I wanted to avoid. I guess that by adding that function, it's a tossup between convenience or lag.

    Re: Vlookup across Multiple Sheets with multiple results


    I'm assuming that without macros, the function of formulas is at its limit with that request, correct?

    Re: Vlookup across Multiple Sheets with multiple results


    LOL, something genius? That already describes what was created prior to this.

    Re: Vlookup across Multiple Sheets with multiple results


    Oh wow! Thank you so much. Now, to modify this, is it the same as the earlier version? Just click and drag down with each formula for additional sheets?I did notice that partial search no longer works though.

    Re: Vlookup across Multiple Sheets with multiple results


    To put in more sheet, type the sheet name underneath the sheet header. To have more room for sheets, just drag the end of the formula (Cols B-E) down. The new formula automatically switches sheets at every purple box (located in Col F). The amount of rows you search for is changed underneath the Row Each header. (e.g. if Row Each is 30 it will do searches for all the sheets from rows 2-31, Row Each = 44 rows 2-45 searched). To fix the single search problem there is a few steps. Put this ---|=IFERROR(MID(RIGHT(Sheet4!$G$2,LEN(Sheet4!$G$2)-SUM(LEN(A$2:$A2))-COUNTA(A$2:$A2)),1,FIND(",",RIGHT(Sheet4!$G$2,LEN(Sheet4!$G$2)-SUM(LEN(A$2:$A2))-COUNTA(A$2:A2)),1)-1),IFERROR(RIGHT(Sheet4!$G$2,LEN(Sheet4!$G$2)-SUM(LEN(A$2:$A2))-COUNTA(A$2:$A2)),""))|--- into A3 and copy down 2 spaces. And underneath headers Search1-4 look at the end of the formula to find isblank($A$#)=FALSE and replace with len($A$#)>0. Then copy those formulas down.

    Re: Vlookup across Multiple Sheets with multiple results


    I tried pasting that formula into A3 on the Function tab and it kept giving me an 'Invalid Formula' error.**EDIT** I got the formula in A3 to work, but the edit to the formula in Search1 gives a #VALUE! error.

    Re: Vlookup across Multiple Sheets with multiple results


    This is in B2


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


    Both A2 and B2 gives the #VALUE! error. I did nothing with A2.

    Re: Vlookup across Multiple Sheets with multiple results


    I'm curious why my modification didn't work. Oh well..


    Thank you for the amazing edit to this workbook. I believe I asked this before, but on the multiple search workbook, to add a new sheet is as simple as pasting the formulas down and adjust the 'Rows Each' cell, correct?

    Re: Vlookup across Multiple Sheets with multiple results


    Add in the new sheet underneath Sheets header. The formulas in columns B-D will change what sheet it's looking at after so many rows determined in Rows Each. So when 30 is in Rows Each it searches rows 2-31 for a match on all the sheets entered. (e.g. F2 is colored Purple cause that's where the first sheet's search begins. Look down Rows Each say 30 and you'll see another purple cell representing the start of the second sheet's search. Down another 30 rows another purple cell and so on and so on)

    Re: Vlookup across Multiple Sheets with multiple results


    Thank you so much for the help! It works perfectly and I managed to extend the ranges from 30 to 125 with little effort.


    Now I need to work on somehow having the matching results highlight to the same color.

Participate now!

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

Register Yourself Login