Forum Discussion

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
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
Sample sheet.xlsm292 KB

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.

mtarler

Silver Contributor

Mar 15, 2021

capeperson In the attached I added some more data. So instead of blank cells you should see something now.
check it out, but if it isn't working can you post what you see on the LookupSheet (I changed that sheet name also). Here is an example what I get for "steve"

Sample sheet.xlsm290 KB

capeperson
Copper Contributor
Mar 15, 2021
It appears that if I enabling editing I the information disappears, but if I leave editing un enabled that I see the results you see on that page.
- mtarler
  Silver Contributor
  Mar 15, 2021
  The problem is the formula pulling the list of sheet names is not working for you. I just looked it up and the function TEXTJOIN was introduced in EXCEL 2019 and hence why you are getting that error. So unfortunately, my little trick to automatically pull the sheet names will not work for you. I could give you a trick that uses the Name Manager, but since you say you have only up to 10 sheets in your workbook maybe it will be best if you do as I mentioned in my earlier post and manually type those sheet names in col A. At least do that to make sure everything else works correctly.
  - capeperson
    Copper Contributor
    Mar 18, 2021
    Thank you very much, that seems to be working out well for me at this point now to just put the final tweaks on the worksheets.
    
    I truly appreciate the time you took to help me!