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
    1. =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


    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


    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


    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
    1. =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


    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.