Vlookup across Multiple Sheets with multiple results

  • 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


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


    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?