Index Match Multiple Criterias

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

WeekMonthYearObjetivoPeríodo
SundayNovember2015#N/AManhã
MondayNovember2015#N/AManhã
TuesdayNovember2015#N/AManhã
WednesdayNovember2015#N/AManhã
ThursdayNovember2015#N/AManhã
FridayNovember2015#N/AManhã
SaturdayNovember2015#N/AManhã
SundayNovember2015#N/AManhã
MondayNovember2015#N/AManhã

<tbody>
</tbody>

WeekMonthPeríodo20152016201720182019
SundayJanuaryManhãabaaa
MondayJanuaryManhãbaaaa
TuesdayJanuaryManhãdaaaa
WednesdayJanuaryManhãdaaaa
ThursdayJanuaryManhãdaaaa
FridayJanuaryManhãdaaaa
SaturdayJanuaryManhãdaaaa
SundayFebruaryManhãdaaaa
MondayFebruaryManhãdaaaa

<tbody>
</tbody>

Thanks in advance!!


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

Some videos you may like

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
Joined
Oct 10, 2011
Messages
4,627
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 27, 2015
Messages
2
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
Joined
Oct 10, 2011
Messages
4,627
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback and welcome to the forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,726
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top