Vlookup across Multiple Sheets with multiple results

  • Re: Vlookup across Multiple Sheets with multiple results


    I do have one question though.


    When replacing the numbers with actual words, they do not show up on Sheet4. It seems to only show numbers, not letters. I tried modifying the data in the sheets and the words appear on the hidden document, but they do not carry over as results.

  • Re: Vlookup across Multiple Sheets with multiple results


    Quote from JSpectrum;562548

    I do have one question though.


    When replacing the numbers with actual words, they do not show up on Sheet4. It seems to only show numbers, not letters. I tried modifying the data in the sheets and the words appear on the hidden document, but they do not carry over as results.


    Bump.

    I'm curious if it has anything to do with the Count feature in the formula. Once actual words are introduced where numbers were, the cells become blank for results.

    Any help on this would be greatly appreciated. Thank you.

  • Re: Vlookup across Multiple Sheets with multiple results


    O sorry didn't see your first post. It's cause the formula underneath All in the hidden sheet find the numbers with Large which only works with numbers. Let me see about fixing that up.

  • Re: Vlookup across Multiple Sheets with multiple results


    I'm assuming that your formula can also be adjusted for different ranges as well. If I wanted the target to be C:D, that is possible?

  • Re: Vlookup across Multiple Sheets with multiple results


    If you want to have a range of certain columns you could try changing the name of the sheets titles on the hidden page to gibberish so that it doesn't get any data.

    Also here is the new workbook. If you look at the formula you can understand why this is impossible to do in one formula (or I'm over thinking it). Regardless, it is done but it has one drawback. The bigger the range of values the formula has to look at the longer it takes and every time you enter a piece of data it recalculates and I can't stop it cause you said I can't use VBA.

    NOTE: If you want it the sheet to only recalculate when you want you could turn calculations to manual, then recalculation only happens when F9 is pressed

    Files

    • Example.xlsx

      (51.45 kB, downloaded 68 times, last: )
  • Re: Vlookup across Multiple Sheets with multiple results


    I figured as much about the calculations. If it's pulling only a few results from one entry, I think it's well worth the recalculating time.


    Regardless, I owe you a huge thanks. Your time spent is much appreciated!

  • Re: Vlookup across Multiple Sheets with multiple results


    Well it was A LOT bigger challenge then I originally thought......but gotta love a challenge LOL :P
    Anyways, glad to have helped!!!

  • Re: Vlookup across Multiple Sheets with multiple results


    Yay! It's me again...

    If I wanted to decrease the 'processing' time for this workbook, is there a way? I'm assuming that due to the size and the cells it is referecning, that's the cause of the load time. I have yet to try a manual processing yet, but these work computers utilize older technology.

    Also, I was curious if there was a way to pull indirect matches as well. For instance, if you search 'word' and it can pull results like word (1) or word 1, etc?

    I'm also driving myself crazy on how to change the sheet names and have it continue to work. I renamed everything possible to match, but it doesn't appear to work once 'Sheet1' is modified.

  • Re: Vlookup across Multiple Sheets with multiple results


    The max number per sheets would be from 1 to possibly 30. I would like a partial find like in my example, across the range C:D but that would be the supreme icing on the cake and nothing that I could find made it possible, even editing the range in the formula.


    On a side note, i'm stuck camping until Monday morning. That explains my offline status until then. Thank you so much for your help!

  • Re: Vlookup across Multiple Sheets with multiple results


    This should be a big increase to speed and does every thing that you asked it to do in your last post.

    Files

    • Example.xlsx

      (35.19 kB, downloaded 73 times, last: )
  • Re: Vlookup across Multiple Sheets with multiple results


    Hi,


    You could try this UDF.



    in dbrown14's workbook


    in C2 and copied down,


    =MLOOKUP_ON_MSHEETS($G$2,"A1:D50",1,4,1,ROWS(C$2:C2),"Sheet1","Sheet2","Sheet3","asdf")



    where:


    A1:D50 is the range to lookup


    1 - Lookup value Col


    4 - Col from where value to be returned


    1 - similar to 'contains' in auto filter,use 0 - for exact match


    ROWS(C$2:C2) - instead of this, you can use 1,2,3... nth


    and the last parameter is sheet names in double quotes separated by comma. (n number of sheets)


    HTH

  • Re: Vlookup across Multiple Sheets with multiple results


    Krishnakumar,A piece of vba code would have made this easier, but jspectrum is using this excel sheet at work where he can't use macros. That's why I had to avoid any macros.dbrown

  • Re: Vlookup across Multiple Sheets with multiple results


    Indeed that may be true. Also if you have a better solution than mine please tell :)I feel like I am over thinking this

  • Re: Vlookup across Multiple Sheets with multiple results


    Hi,


    Here is another approach..


    In F4 to F7 and copied across


    =INDEX(INDIRECT("'"&$E4&"'!d1:d50"),SMALL(IF(ISNUMBER(SEARCH(G2,INDIRECT("'"&$E4&"'!a1:a50"))),ROW(Function!$A$1:$A$50)),COLUMNS($F4:F4)))


    where:


    E4:E7 write all the sheet names.


    It's an array formula. [ea]*[/ea]


    To mask the errors use conditional formatting.


    Select the range, in CF, formula is: =ISERROR(F4) > change the font color to white.

  • Re: Vlookup across Multiple Sheets with multiple results


    Thank you all for the help! Yes, at my job, we aren't able to use macros due to a supposed 'security hazard' and the formulas have been very limiting. dbrown, your work is amazing as always. Am I able to modify it to pull from the range C:D myself? I was also curious how easy it is to change the sheet names. On the previous version, I could not, for the life of me, figure out why the name change broke the formula. On a side note, erase some of your private messages, dbrown. lol

  • Re: Vlookup across Multiple Sheets with multiple results


    For some reason, it would not let me edit my previous post. I attempted to modify the sheet names on the new version, both on the actual tab and in 'Function'. Once the sheet is named something different, Sheet4 will no longer recognize it.

  • Re: Vlookup across Multiple Sheets with multiple results


    On the "Function" sheet, try changing "Sheet1" or "Sheet2" to "asdf" in my example workbook. It works for me. If it doesn't work for you let me know.