Index Match Multiple Criterias

V

Visiedo

New Member
Joined
Oct 27, 2015
Messages
2
Hi guys, I'm new to this forum, nice to meet you all
I actually created this account in the hope that somebody could help me
I am trying to make an index match formula that will look for values considering 4 total criterias (3 columns and 1 row)

I wanna get a result that matches "week" "month" "período" and "year", so January monday manhã of 2015 will give me the desired result however all i can get is error N/A meaning it didnt find a resut
I am using a formula with just 3 criterias (2 columns and 1 row) and still got no good results, I figured that should only go for 4 criterias after made 3 criterias work
So I tested in another sheet "Tabela Objetivos" with a small sample and it worked, I dont know why(only 3 criterias worked- Tried with 4 and didnt work)
I tried so much and got nothing
So i am asking for someone more experienced than me a little help
I am posting a link to access to the sheet from google drive (i read the posting section rules and found nothing that said I couldnt do it, so I hope i really can)
But if anything goes wrong, it is supossed to look like this: (just copied little part from the tables as matter of illustration: The main one has over 1500 rows and the other 250)

Formula Used:=INDEX($AE$4:$AJ$245,MATCH(B4&D4,$AB$4:$AB$245&$AC$4:$AC$245,0),MATCH(E4,$AE$3:$AJ$3,0)Used criterias Week Month and Year - and also used control shift enter

[TABLE="width: 662"]
<tbody>[TR]
[TD]Week[/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Objetivo[/TD]
[TD]Período[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"][/TD]
[TD]November[/TD]
[TD="align: right"]2015[/TD]
[TD="align: center"]#N/A[/TD]
[TD]Manhã[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 423"]
<tbody>[TR]
[TD]Week[/TD]
[TD]Month[/TD]
[TD]Período[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]January[/TD]
[TD]Manhã[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]January[/TD]
[TD]Manhã[/TD]
[TD]b[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]January[/TD]
[TD]Manhã[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]January[/TD]
[TD]Manhã[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]January[/TD]
[TD]Manhã[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]January[/TD]
[TD]Manhã[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]January[/TD]
[TD]Manhã[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]February[/TD]
[TD]Manhã[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]February[/TD]
[TD]Manhã[/TD]
[TD]d[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!!


HTML: 
https://drive.google.com/file/d/0Bwb5OeJSR3hYcnlrUDV1VWlVU0k/view?usp=sharing
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
I think the issue is cell E3 for year in your worksheet is entered as a number, but Excel changes headers in tables to text. So try entering your year in E3 as text or change the worksheet formula in E4 to:

Code: 
=INDEX(D11:H15,MATCH(B3&C3&D3,Table14[Semana]&Table14[Mês]&Table14[periodo],0),MATCH([COLOR=#ff0000]TEXT(E3,"#")[/COLOR],Table14[[#Headers],[2015]:[2019]],0))
As you know this is an array formula and must be entered with CTRL-SHIFT-ENTER.
 
Upvote 0
AhoyNC said:
I think the issue is cell E3 for year in your worksheet is entered as a number, but Excel changes headers in tables to text. So try entering your year in E3 as text or change the worksheet formula in E4 to:

Code: 
=INDEX(D11:H15,MATCH(B3&C3&D3,Table14[Semana]&Table14[Mês]&Table14[periodo],0),MATCH([COLOR=#ff0000]TEXT(E3,"#")[/COLOR],Table14[[#Headers],[2015]:[2019]],0))
As you know this is an array formula and must be entered with CTRL-SHIFT-ENTER.
Click to expand...

Thank you very much sir, it worked when I changed the formula as you told me to
 
Upvote 0
You're welcome. Thanks for the feedback and welcome to the forum.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Salesman with highest billing amount
2
Replies
12
Views
320
Fluff
Fluff
I
  • Solved
Formula help - Index, Match, SumProduct/SumIFs? Which one?
Replies
4
Views
120
Eric W
Eric W
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
627
Peter_SSs
Peter_SSs
S
  • Question Question
Employee Training Tracker
Replies
0
Views
222
Schuyla
S
A
  • Question Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
493
Anthony86
A

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top