Forum Discussion
Help with Index and Match Formula - Excel 2013
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
Perhaps you may share the file with formula which returns such error? In current file your formula works as expected on two sheets
or
- 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.
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.