# index match with indirect across multiple sheets

1. ## index match with indirect across multiple sheets

i have a workbook w/ 9 data tabs and an collaboration tab. What i am trying to do is pull the data from the 9 data tabs and insert the data on the collaboration tab. the data tabs are named by a group of people but the columns are all named the same in each tab (just different people in each). I have been trying without success to get this to work. I tried both index match and vlookup with the indirect function but keep getting errors. i have created a named range to list out all the tab names and used in the indirect function but i don't know what i am doing wrong. below are both the vlookup as well as the index match formulas i have tried. ANY feedback would be great!

=INDEX(INDIRECT("'"&tabs&"'!a1:y100"),MATCH(A3,INDIRECT("'"&tabs&"'!a1:a100"),0),MATCH(B1,INDIRECT("'"&tabs&"'!a1:y1"),0))
=VLOOKUP(A4,indirect("'"&tabs&"'!\$A\$3:\$Y\$17"),2,FALSE)

2. ## Re: index match with indirect across multiple sheets

Originally Posted by mamachrissy1028

=INDEX(INDIRECT("'"&tabs&"'!a1:y100"),MATCH(A3,INDIRECT("'"&tabs&"'!a1:a100"),0),MATCH(B1,INDIRECT("'"&tabs&"'!a1:y1"),0))

=VLOOKUP(A4,indirect("'"&tabs&"'!\$A\$3:\$Y\$17"),2,FALSE)
If "tabs" refers to a range of cells then you'd have to select an array of cells equal to the size of "tabs" then array enter the formula.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

3. ## Re: index match with indirect across multiple sheets

I have all of my tabs listed out in a table to reference the names of the tabs i want it to search for the data. this is the named range "'"&tabs&"' that i have used in the formula. the ranges that i have specified later in the formula is the array that i want to index and search for the data that i want to pull. I don't understand why this would be wrong.

4. ## Re: index match with indirect across multiple sheets

Because you're referencing an ARRAY of sheet names so the formula is doing an ARRAY of lookups and it can't return an ARRAY of lookups to a single cell.

=INDEX(INDIRECT("'"&tabs&"'!a1:y100"),MATCH(A3,INDIRECT("'"&tabs&"'!a1:a100"),0),MATCH(B1,INDIRECT("'"&tabs&"'!a1:y1"),0))

=VLOOKUP(A4,indirect("'"&tabs&"'!\$A\$3:\$Y\$17"),2,FALSE)

"tabs" should refer to a single sheet name.

5. ## Re: index match with indirect across multiple sheets

OK, so then how do i look for the data on multiple sheets and return the value i am looking for? I thought that the indirect function would look for the name across several sheets and return the value where the row and column intersect on the sheet that contained the name.

6. ## Re: index match with indirect across multiple sheets

Will the name appear on all the sheets, just a single sheet or multiple sheets?

7. ## Re: index match with indirect across multiple sheets

The names are set up in groups based on their specialty. It will appear on single or multiple sheets but not all depending if their specialty shifts at any time. However, the columns it is looking up will have empty cells if they are no longer a part of that group.

8. ## Re: index match with indirect across multiple sheets

I'm totally confused!

I would need to see the file to figure out what you're trying to do. From the sound of it, it's probably a big file.

9. ## Re: index match with indirect across multiple sheets

Here is an example of what i am working on. The tasked seemed simple enough, but i don't know what i am doing wrong....

test.2.xlsx

10. ## Re: index match with indirect across multiple sheets

Since the data on the Dept sheets is numeric it looks like what you want to do is a SUMIF across the sheets?

11. ## Re: index match with indirect across multiple sheets

I don't want to sumif b/c each agent can only be counted once per column as they can only have 1 specialty. If for some reason there is an error i don't want there to be a 2 but to rather throw an error.

12. ## Re: index match with indirect across multiple sheets

1st, you actually dont have (that I can find) a table or range name with all tab names, so I made 1 on Cheats sheet, and called it Depts. Just a simple table with Dept 1 - Dept 9, then gave it the range name of Depts

=SUMPRODUCT(SUMIF(INDIRECT("'"&Depts&"'!A3:A100"),\$A3,INDIRECT("'"&Depts&"'!"&CHAR(97+COLUMN()-1)&"3:"&CHAR(97+COLUMN()-1)&"100")))

13. ## Re: index match with indirect across multiple sheets

I have no idea what result you expect!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1