Forum Discussion

capeperson's avatar
capeperson
Copper Contributor
Mar 11, 2021
Solved

Help with Index and Match Formula - Excel 2013

I am working on a project at work which requires us to have a workbook with anywhere from 1 to 10 sheets of data.  On the last sheet we have a Contract that needs to be printed and signed by our clie...
excel
  • mtarler's avatar
    mtarler
    Mar 12, 2021

    capeperson In the attached I mocked up something that is working

    a) I unmerged A1 on your 10 sheets and made A2 be the "ADDRESS" title and put a formula in the new A1 that will pull the sheet name (I did that so I didn't have to manually type each sheet name and if you copy the sheet or change the sheet name it auto updates, but you could manually insert the sheet name there)

    b) I added a new sheet and in Col A I used a formula to collect all those sheet names.  Again, you could just skip to this step and manually type all the sheet names 

    c) I do a match for that name on all the sheets

    d) I have cells in row 1 to identify the sheet name and the corresponding row that the name is found

    e) I then used an INDIRECT formula to use that sheet name (I named that cell "sName") and row ("sRow") to pull that row and the INDEX is used to pull which column has the info you want.  I only did a few examples to give you an idea of how it works.  I also tried to use all 'older' non-DA formulas because you mentioned you are on an older Excel.

     

    see attached

mtarler's avatar
mtarler
Silver Contributor
Mar 12, 2021
You can't use '&' to combine ranges like that. There are different option but if it is just those 2, maybe consider nesting them with an IFERROR(LOOKUP 1st range, LOOKUP 2nd range) If it is across 10 sheets you might want to consider creating a helper sheet where you pull all the relevant data together for a single lookup range. (This is completely opposite of what @matheletes would say as it would be better to enter the data into a single entry table and then you can have the individual sheets have sub-tables or pivot tables or just have 1 sheet where you can select / slice what you want to lookup/see). If you provide a sample workbook we could certainly help more/easier.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Mar 12, 2021

      capeperson In the attached I mocked up something that is working

      a) I unmerged A1 on your 10 sheets and made A2 be the "ADDRESS" title and put a formula in the new A1 that will pull the sheet name (I did that so I didn't have to manually type each sheet name and if you copy the sheet or change the sheet name it auto updates, but you could manually insert the sheet name there)

      b) I added a new sheet and in Col A I used a formula to collect all those sheet names.  Again, you could just skip to this step and manually type all the sheet names 

      c) I do a match for that name on all the sheets

      d) I have cells in row 1 to identify the sheet name and the corresponding row that the name is found

      e) I then used an INDIRECT formula to use that sheet name (I named that cell "sName") and row ("sRow") to pull that row and the INDEX is used to pull which column has the info you want.  I only did a few examples to give you an idea of how it works.  I also tried to use all 'older' non-DA formulas because you mentioned you are on an older Excel.

       

      see attached

      • capeperson's avatar
        capeperson
        Copper Contributor
        Mar 15, 2021
        Sorry to be a pain, I am muddling around in the Sample sheet and I can't see to get any results with the information you entered on the sample sheet. I can get "Sue" and "not bob" to work to pull the street name, but that is using Sheet 10 and the old code I had in, when I use "Steve" or "Bob" on Sheet 1 and Sheet 6 respectively, I get no results at all, for any of the boxes in the Contract.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 12, 2021

      capeperson 

      Could you please clarify what exactly doesn't work. I copy/paste formula from your initial post into the second sheet and it works

      • capeperson's avatar
        capeperson
        Copper Contributor
        Mar 12, 2021
        On the Contract Sheet I require that I can enter into G21 , whatever name is entered on any of the Sheets 1 - 10 from Column B. In my sample file for me, using the original code in C7, and using the name from Sheet 9, PERSON, CAPE or the Kent, Clark name from sheet 10, I end up with a #VALUE! error in cell C7 of the Contract Sheet.

Resources