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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
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
 
Upvote 0
You're welcome. Thanks for the feedback and welcome to the forum.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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