# Match/Index over multiple tabs

1. ## Match/Index over multiple tabs

Hi,

I am trying to do a match/index over multiple tabs but it will not work.

My formula is located in the 'Design' tab in the F Column, see the attached file

=IF(ISNA(MATCH(\$D2,'F1'!\$A\$2:\$A\$200,0)),0,INDEX('F1'!\$G\$2:\$G\$200,MATCH(\$D2,'F1'!\$A\$2:\$A\$200,0)))

This is the formula that works for one tab, the "F1" tab in this case.

So basically, it compares the value of the D column in the Design tab with the value in the F1 tab at a certain column and return the price if the cells matches. (Column F = Prix, which is "price in french")

Does anyone know how to make it work over multiple tabs, say 40 tabs ?

Best regards,

2. ## Re: Match/Index over multiple tabs

Question: what differentiates F1 from F40?

3. ## Re: Match/Index over multiple tabs

Originally Posted by JohnTopley
Question: what differentiates F1 from F40?
Each tab would represent a different order.

4. ## Re: Match/Index over multiple tabs

I would recommend you have a single table with a column representing what is currently the tab name (F1, F2 etc).

5. ## Re: Match/Index over multiple tabs

Originally Posted by JohnTopley
I would recommend you have a single table with a column representing what is currently the tab name (F1, F2 etc).
This would not work as I would like to be able to have twice the same row but with a different quantity in the same sheet and it doesnt add up correctly if I have more than one row with the same name.

Any other ideas ?

Thanks,

6. ## Re: Match/Index over multiple tabs

I don't understand your logic: you could have 20 rows with "Bananas" with an "Order Number" for each row. You can summarise by Order or any other parameter.

7. ## Re: Match/Index over multiple tabs

Originally Posted by JohnTopley
I don't understand your logic: you could have 20 rows with "Bananas" with an "Order Number" for each row. You can summarise by Order or any other parameter.
I tried to summarize by order but the right amount will not update correctly in E column of the tab "Design".

As you can see in the attached file, see the F1 tab for example.

I might be misunderstanding what you are proposing, sorry if this is the case.

Best regards,

8. ## Re: Match/Index over multiple tabs

To SUM .

in E2.

=SUMIFS('F1'!C:C,'F1'!B:B,Design!D2)

Copy down

9. ## Re: Match/Index over multiple tabs

Originally Posted by JohnTopley
To SUM .

in E2.

=SUMIFS('F1'!C:C,'F1'!B:B,Design!D2)

Copy down
Seems to be working, thanks for your help and have a nice day.

10. ## Re: Match/Index over multiple tabs

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

11. ## Re: Match/Index over multiple tabs

Here is the macro code to fill formulas given by JohnTopely , with slight modification ,in range G2 to last row Last column. Filling manually is a difficult job.
``Please Login or Register  to view this content.``

##### Users Browsing this Thread

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