# Index Match Multiple Criterias

#### Visiedo

##### New Member
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

 Week Month Year Objetivo Período Sunday November 2015 #N/A Manhã Monday November 2015 #N/A Manhã Tuesday November 2015 #N/A Manhã Wednesday November 2015 #N/A Manhã Thursday November 2015 #N/A Manhã Friday November 2015 #N/A Manhã Saturday November 2015 #N/A Manhã Sunday November 2015 #N/A Manhã Monday November 2015 #N/A Manhã

<tbody>
</tbody>

 Week Month Período 2015 2016 2017 2018 2019 Sunday January Manhã a b a a a Monday January Manhã b a a a a Tuesday January Manhã d a a a a Wednesday January Manhã d a a a a Thursday January Manhã d a a a a Friday January Manhã d a a a a Saturday January Manhã d a a a a Sunday February Manhã d a a a a Monday February Manhã d a a a a

<tbody>
</tbody>

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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### AhoyNC

##### Well-known Member
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.

#### Visiedo

##### New Member
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.

Thank you very much sir, it worked when I changed the formula as you told me to

#### AhoyNC

##### Well-known Member
You're welcome. Thanks for the feedback and welcome to the forum.

Replies
9
Views
114
Replies
3
Views
103
Replies
10
Views
176
Replies
14
Views
192
Replies
3
Views
109