SOLVED

Help with Index and Match Formula - Excel 2013

Copper Contributor

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 clients. The Data on the other sheets is the information from the client that needs to be on the contract. 

 

At this point I have set up the contact to pull the information from 1 sheet onto the contract by entering the Clients name into cell G21 and then using index and match the rest of the cells auto populate.  It works great. 

 

When I went to add the rest of the sheets to the formula in the required cells I am getting the error of #VALUE!

 

Can anyone tell me what I have done wrong in this code?

 

=INDEX('Sheet 10T'!C3:C122&'Sheet 9'!C3:C72,MATCH(Contract!G21,'Sheet 10T'!B3:B122&'Sheet 9'!B3:B72,0))&""

 

We have Excel 2013 in my office.

I am very new to any intermediate or advance Excel functions and having been watching YT videos and reading various forms to gain insight into getting Excel to do some of the advanced for me features I would like.  

 

 

15 Replies
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.

Thank you, I will upload a sample file.  

 

sorry for all the edits, I uploaded a sample sheet blank and sample 2 with an entry on the forms.

@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

image.png

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.

@capeperson 

Perhaps you may share the file with formula which returns such error? In current file your formula works as expected on two sheets

image.png

or

image.png

Sergei, have you entered data onto each of the sheets? because if you are searching for "KENT, CLARK" and you have "KENT, CLARK" on sheet 9 row 3 and "PERSON, CAPE" on sheet 10T row 3 then wont that formula will be looking for "KENT, CLARK" but see "PERSON, CAPEKENT, CLARK". Furthermore, the indexed value will return a concatenation from those multiple sheets also.
best response confirmed by capeperson (Copper Contributor)
Solution

@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 

You are right, but in this case formula returns #N/A error, not #VALUE! error as it was declared. 

For me it still produces a #VALUE! error in the cell that I have the formula entered in. I didn't bother entering it into any of the other cells until I had it working.

Thank you for all this, I can't wait to check it out and see how it works, I for sure will have to spend time on it to figure it out.

 

I very much appreciate the time and effort you took in helping me solve my problem.

Do you have any recommended courses or books to learn more about Excel?

 

 

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.

@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"

mtarler_0-1615814959952.png

 

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.

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.
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!
1 best response

Accepted Solutions
best response confirmed by capeperson (Copper Contributor)
Solution

@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

View solution in original post