Vlookup across Multiple Sheets with multiple results

    Hello,

    I am trying to work out a formula that will search through multiple sheets. If information is entered into 'G2', this formula would look for corresponding data throughout the sheets and place the results.

    The formula below is what I have thus far. It seems to stop at the first match and goes no further. Due to security at my job, I can not utilize a macro or attach an example of the worksheet.

    Code
    =IFERROR(VLOOKUP(G2,'Sheet1'!A5:D124,4,TRUE),IFERROR(VLOOKUP(G21,'Sheet2'!A3:D120,4,TRUE),IFERROR(VLOOKUP(G2,'Sheet3'!A4:D120,4,TRUE),"N/A")))



    Please let me know if any additional information is needed. Thank you for the help!

    Re: Vlookup across Multiple Sheets with multiple results


    Your formula is set up to find the first match then stop. What were you hoping it would do?

    Re: Vlookup across Multiple Sheets with multiple results


    I was hoping that it would find the first match, then continue to the next and repeat until all matches are found, then display every match in a specified cell.

    Re: Vlookup across Multiple Sheets with multiple results


    So what would that cell value look like after your formula did its job?

    Re: Vlookup across Multiple Sheets with multiple results


    For example,

    If, in G2, I type 'Ice Cream'

    It would search through every list and within the cell that has the formula, it would display "Chocolate Vanilla Strawberry'. Or something along those lines. The results would be displayed in one cell, unless there is a way to create a drop-down list or have it displayed in multiple cells.

    Re: Vlookup across Multiple Sheets with multiple results


    I can display the results in multiple cells. I could have a formula in a few minutes if you want that? The cells the formula goes into though cannot be merged.

    Re: Vlookup across Multiple Sheets with multiple results


    That would be perfect. My largest drawback was just to get the multiple results displayed. Thank you for the help!

    Re: Vlookup across Multiple Sheets with multiple results


    Your problem is proving to be rather difficult to be compatible with 2007. Are you only going to be looking through sheets 1-3?

    Re: Vlookup across Multiple Sheets with multiple results


    No, there are currently 7 pages to search through and a few (3-4) may be added in the future.

    Re: Vlookup across Multiple Sheets with multiple results


    Ok. Unfortunately, I'm going to need more time to think about this then I originally thought. I'll keep you posted.

    Re: Vlookup across Multiple Sheets with multiple results


    Ok the easy part was making it work for one sheet but to make one formula that goes through all the sheets is becoming a challenge and the formula gets crazy complicated.

    This formula makes it work for one sheet.

    =IFERROR(INDEX(Sheet1!$D$1:$D$30,IF(ROWS($A$1:A1)<COUNTIF(Sheet1!$A$1:$A$30,"="&$G$2),LARGE(ROW(Sheet1!$A$1:$A$30),ROWS($A$1:A1)),-1)),"")

    Re: Vlookup across Multiple Sheets with multiple results


    I applied it to my workbook and it only creates a blank cell with no data. I'm curious if I have designated the correct rows to look in. Currently, the sheets utilize A3 through D126.

    Code
    =IFERROR(INDEX(Sheet1!$A$1:$D$126,IF(ROWS($A$5:D126)<COUNTIF(Sheet1!$A$5:$D$126,"="&$G$2),LARGE(ROW(Sheet1!$A$5:$D$126),ROWS($A$5:D126)),0)),"")



    Thank you for the help.

    Re: Vlookup across Multiple Sheets with multiple results


    Sorry. I forgot to say when entering the formula press Ctrl+Shift+Enter rather than just Enter. You should see the first answer. Then you can drag the formula down a column to get the rest of the results.

    Re: Vlookup across Multiple Sheets with multiple results


    I pasted the formula and used Ctrl+Shift+Enter to apply, then dragged downward, but it still displays a blank cell where the formula rests.

    Re: Vlookup across Multiple Sheets with multiple results


    Here is a sample workbook. It contains a very hidden sheet which mean that you are not able to see unless you hit Alt + F11 and change sheet20(Function)'s visible property to visible. Hopefully this is something that can benefit you. Sorry it's not one simple formula.

    Re: Vlookup across Multiple Sheets with multiple results


    Holy cow! This is simply amazing. Thank you so much for this reference and the hard work you have put into this.

Participate now!

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

Register Yourself Login