match with date and 2nd option

Raanan

Board Regular
Joined
May 22, 2013
Messages
122
Good morning Happy New Year and apologies for the "noob" question. For soem reason my head wont get around this.



I have a simple table A1:E10
I have dates in column A and headings on row 1.

Building a minor summary dashboard what I need to be able to do is have a formula that has will search the table and return the answer based ont he criteria.

Bearing in mind the date on the dashboard can be changed.


So I'd need to have a formula to search Column A and have it match with criteria in Row 1.


SO if the headers were:


Date
NewexistingInboundOutbound
01/01/2014
1234
02/01/20145678
03/01/20149101112
04/01/201413141516
05/01/201417181920
06/01/201421222324
07/01/201425262728
08/01/201429303132
09/01/201433343536

<tbody>
</tbody>


If "B2" on the dashboard had "02/01/2014"
and "b3" had "Existing"

Id want the result "6"


I've tried index match, but unless I havent used it correctly (which is always a chance) I cant get it to work.

Regards.



(ps lets say the data is on sheet 1 and the dashboard on a sheet called "Dash")
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

StefanVL

Board Regular
Joined
Mar 7, 2013
Messages
146
Hello there,

this should solve your problem:

=INDEX($B$2:$E$10;MATCH(Dash!$B$2;$A$2:$A$10;1);MATCH(Dash!$B$3;$B$1:$E$1;0))
 

Raanan

Board Regular
Joined
May 22, 2013
Messages
122
thanks guys, had a feeling it was an index match but today, my head isnt working..

Regards
 

StefanVL

Board Regular
Joined
Mar 7, 2013
Messages
146

ADVERTISEMENT

Glad I could help!
 

Raanan

Board Regular
Joined
May 22, 2013
Messages
122
What if i had a third match to find?

So, if i had date added say a name in Column B, and needed to search name and date AND heading?

DO i Just add a new Match to it?
 

StefanVL

Board Regular
Joined
Mar 7, 2013
Messages
146

ADVERTISEMENT

What if i had a third match to find?

So, if i had date added say a name in Column B, and needed to search name and date AND heading?

DO i Just add a new Match to it?

That would not work. You would have to make it an array formula to make it work, making it such as this example:

DateNameNewexistingInboundOutbound
1-1-2014Tom1234
1-1-2014Jane5678
1-2-2014Dennis9101112
1-2-2014Tom13141516
1-2-2014Jane17181920
1-2-2014Eric21222324
1-3-2014Eric25262728
2-3-2014John29303132
3-3-2014Tom33343536

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Say you want:
1-2-2014
Tom
Existing

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

which are in dash B2:b4

you'd enter the following formula:

=INDEX($C$2:$F$10;MATCH(1;IF($A$2:$A$10=Dash!$B$2;IF($B$2:$B$10=Dash!$B$3;1;""));0);MATCH(Dash!$B$4;$C$1:$F$1;0))

Entering the formula with ctrl+shift+enter as it is a array formula.

The result of the formula would net you the awnser 14.

Hope this helps,

Stefan
 

Raanan

Board Regular
Joined
May 22, 2013
Messages
122
i'#ve tried that and it comes back with N/A ... hmmm

edit: nvm, my bad.. :)
 
Last edited:

Raanan

Board Regular
Joined
May 22, 2013
Messages
122
yeh i think the issue was with the format of the cells, usually happens when working with dates
 

Forum statistics

Threads
1,137,294
Messages
5,680,660
Members
419,923
Latest member
Kalthus

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
Top