Vlookup across Multiple Sheets with multiple results

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Vlookup across Multiple Sheets with multiple results


    One final question, I tried to adjust 'Function' so that each sheet could hold 125 rows of results. As I moved each 'sheet' name to its respective number, it fails to capture the higher numbers. Is there a change in the formula that I am overlooking to capture up to 125 results?

    Re: Vlookup across Multiple Sheets with multiple results


    Quote from JSpectrum;563273

    One final question, I tried to adjust 'Function' so that each sheet could hold 125 rows of results. As I moved each 'sheet' name to its respective number, it fails to capture the higher numbers. Is there a change in the formula that I am overlooking to capture up to 125 results?

    Bump.

    Re: Vlookup across Multiple Sheets with multiple results


    All the formulas stretch out 100 rows. If you are wanting to extend that, select the last one (should be in row 99, 199, 299, etc) and copy the formula down. You should be able to see some of the references in the formula changing as you copy down.

    Re: Vlookup across Multiple Sheets with multiple results


    Quick question...I attempted to add another sheet, so I dragged rows A-C from 'Function' down, then dragged the formulas from E-G to follow and labeled the new sheet. It does not read from the new sheet. I am curious if there is more to do?

    Re: Vlookup across Multiple Sheets with multiple results


    Are the values from the new sheet being showed up in "Function"? Did you make sure the col A-C reference the entire new area that you extended? Did you spell the sheet name correctly? If you checked all these questions and still have a problem check back with me.

    Re: Vlookup across Multiple Sheets with multiple results


    Yes, the results are being displayed in 'Function' and I extended from 600 rows to 1000.

    Re: Vlookup across Multiple Sheets with multiple results


    Is the formula being copied down correctly? When you highlight the last cells say A100:C100 and you copy down to say row 200. The formula in A100 should contain ROWS($A$2:A100) somewhere. The formula in A200 should contain ROWS($A$2:A200) somewhere. When you select A140, can you see brackets around the formula? This {=FORMULA} not =FORMULA All the formulas in columns A-C have extended ranges to contain the values you added in D-F?

    Re: Vlookup across Multiple Sheets with multiple results


    Column C with updated code looks like...

    Code
    =INDEX($G$1:$G$1000,IF(LARGE(ROW($E$1:$E$1000)*($E$1:$E$10000),ROWS($A$2:C550))=0,-1,LARGE(ROW($E$1:$E$1000)*($E$1:$E$10000),ROWS($A$2:C550))))

    And column G looks like

    Code
    =IFERROR(IF(AND(COUNTIF(INDIRECT(CONCATENATE("'",$H$450,"'!",CELL("address",A2))),"=*" & Inquiry!$G$2 & "*")=1,ISBLANK(Inquiry!$G$2)=FALSE),INDIRECT(CONCATENATE("'",$H$450,"'!",CELL("address",D2))),0),0)

    They have the same targets, but don't show up in Sheet4.That was at row 550.

    Re: Vlookup across Multiple Sheets with multiple results


    The first formula you showed me ROW($E$1:$E$1000)*($E$1:$E$10000), I think that your problem is that you have $E$10000 not $E$1000

    Re: Vlookup across Multiple Sheets with multiple results


    That may have been a typo on my end while posting the formula. It would'nt paste completely, so I had to modify outside of Excel.

    Re: Vlookup across Multiple Sheets with multiple results


    Well if it's showing up on the right columns and not of the left columns then the left columns' formulas must be messed up. Try deleting all of columns A-C EXCEPT the header and first row. Edit the formulas in A-C to contain all the data in it's corresponding right side columns. Then select A-C and copy down using the copy handler. NOTE: When done entering the formulas in A-C press CTRL+ALT+ENTER not just ENTER

    Re: Vlookup across Multiple Sheets with multiple results


    I tried that, and for some reason, it just won't register the new sheet. The results still show on Function, but will not show on Sheet4. Some code samples...

    Code
    =IFERROR(IF(AND(COUNTIF(INDIRECT(CONCATENATE("'",$H$450,"'!",CELL("address",A2))),"=*" & Inquiry!$G$2 & "*")=1,ISBLANK(Inquiry!$G$2)=FALSE),INDIRECT(CONCATENATE("'",$H$450,"'!",CELL("address",D2))),0),0)

    That's from G on line 600.

    Code
    =INDEX($G$1:$G$1000,IF(LARGE(ROW($E$1:$E$1000)*($E$1:$E$10000),ROWS($A$2:C600))=0,-1,LARGE(ROW($E$1:$E$1000)*($E$1:$E$10000),ROWS($A$2:C600))))

    and that's from C on line 600...

    Re: Vlookup across Multiple Sheets with multiple results


    Ok. Upload your exact sample file with private data replaced with dummy data. I'll take a look

    Re: Vlookup across Multiple Sheets with multiple results


    Actually, I just figured it out. It was a number issue on where each page was located.

    Re: Vlookup across Multiple Sheets with multiple results


    Another idea, but I don't think this is possible without reworking the formulas. If you type in a search for two things in G2, is it possible to pull up multiple results?

    Re: Vlookup across Multiple Sheets with multiple results


    It would take some work, but I could probably pull it off. Ill probably go with more columns off to the right. And G2 would look similar to "name1,name23". Is that something you'd want me to look into?

    Re: Vlookup across Multiple Sheets with multiple results


    If it's not too much trouble. But yes, I was thinking a comma would be the best seperater for results or for the search. I'm just trying to stay ahead of the curve with creative thinking. Would it be a huge hassle?

Participate now!

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

Register Yourself Login