# Index and Match Multiple Header (Rows) Criteria

1. ## Index and Match Multiple Header (Rows) Criteria

Attachment 150008Hello Excel Gurus,

I have a simple question for you! (It's hard to me though).

Below is my data.
B1-E1: Gross Revenue (Merged Cell)F1-I1: Net Revenue (Merged Cell)
A2: Material B2: Q1 C2: Q2 D2: Q3 E2: Q4 F2: Q1 G2: Q2 H2: Q3 I2: Q4
A3: XYZ B3: 100 C3: 125 D3: 150 E3: 125 F3: 80 G3: 90 H3: 100 I3: 85

So basically, What I need is that, through the formula, Index, Match, Vlookup etc, whatever is the most effective and efficient way, When the material is XYZ for Gross Revenue Q3, the value return should be 150. For material XYZ for Net Revenue Q2, the answer is 90

ps: I've tried to make it look good in here, but you can't understand what I'm trying to do, I have an attached spreadsheet.

2. ## Re: Index and Match Multiple Header (Rows) Criteria

If your inputs: Material, Revenue type, quarter are in N1:N3, respectively, then try:

=INDEX(INDEX(\$B\$3:\$I\$3,MATCH(N2,\$B\$1:\$I\$1,0)):\$I\$8,MATCH(N1,\$A\$3:\$A\$8,0),MATCH(N3,INDEX(\$B\$2:\$I\$2,MATCH(N2,\$B\$1:\$I\$1,0)):\$I\$2,0))

3. ## Re: Index and Match Multiple Header (Rows) Criteria

NBVC,

Thanks for helping. I should have more specific in terms of how the "result" is going to look at. I've updated the spreadsheet, and hopefully it'll be more understandable for you.

In short, the format of is going to be the same too...where the rows have 2 headers, the gross revenue and net revenue merged, and below of each were the quarters. As well, the left hand column would be the material.

PS: Notice that I Puposely make the the "Net Revenue" to appear first and the material to be descending.

4. ## Re: Index and Match Multiple Header (Rows) Criteria

In B15:

``Please Login or Register  to view this content.``
copied down and across.

5. ## Re: Index and Match Multiple Header (Rows) Criteria

Thanks NBVC!!! You're indeed a Guru in Excel.

Just wondering if you could explain how the formula works?

6. ## Re: Index and Match Multiple Header (Rows) Criteria

It's basically a regular Index/Match function with some nested Index/Matches to determine where to start indexing from based on the Revenue type header.

This part: INDEX(\$B\$3:\$I\$3,MATCH(LOOKUP(REPT("z",255),\$B\$13:B\$13),\$B\$1:\$I\$1,0)):\$I\$8 determines which of the revenue groups to look in. The LOOKUP(REPT("z",255),\$B\$13:B\$13) parts looks for the last text string as you copy across cells in row 13 (notice as you copy across, \$B\$13:B\$13 becomes \$B\$13:C\$13, \$B\$13:D\$13 and so on). All cells in columns B:D will reference the revenue in B13 and all cells in column E:I will reference the revenue in E13... In this way all cells under the merged headers will reference the corresponding header.

Once that header string is determined, the MATCH() finds the same header in B1:I1 and that is Indexed to same position in B3:I3 and that is the "upper left" corner of your range. The I8 is fixed as the "bottom right" corner.

Similar for the other MATCH() function. Once the start is determined, then the rest is a normal INDEX/MATCH formula.

7. ## Re: Index and Match Multiple Header (Rows) Criteria

Although I still completely do not understand, but will take the time to read, digest, over and over again. Hopefully will understand it one day.

Thanks once again.

8. ## Re: Index and Match Multiple Header (Rows) Criteria

You could also shorten with HLOOKUP.

``Please Login or Register  to view this content.``
copy across & down.

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

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.06497 seconds
• Memory Usage 9,050KB
• Queries Executed 15 (?)
Template Usage (32):
• (2)bbcode_code
• (1)footer
• (1)forumrules
• (1)gobutton
• (8)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (8)postbit_legacy
• (8)postbit_onlinestatus
• (8)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (47):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit
• tag_fetchbit_complete
• forumrules