match with date and 2nd option

[Raanan]

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	New	existing	Inbound	Outbound
01/01/2014	1	2	3	4
02/01/2014	5	6	7	8
03/01/2014	9	10	11	12
04/01/2014	13	14	15	16
05/01/2014	17	18	19	20
06/01/2014	21	22	23	24
07/01/2014	25	26	27	28
08/01/2014	29	30	31	32
09/01/2014	33	34	35	36

<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")

 

[StefanVL]

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))

 

[CROY1985]

previous poster got there first.

 

[Raanan]

thanks guys, had a feeling it was an index match but today, my head isnt working..

Regards

 

[StefanVL]

Glad I could help!

 

[Raanan]

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]

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:

Date	Name	New	existing	Inbound	Outbound
1-1-2014	Tom	1	2	3	4
1-1-2014	Jane	5	6	7	8
1-2-2014	Dennis	9	10	11	12
1-2-2014	Tom	13	14	15	16
1-2-2014	Jane	17	18	19	20
1-2-2014	Eric	21	22	23	24
1-3-2014	Eric	25	26	27	28
2-3-2014	John	29	30	31	32
3-3-2014	Tom	33	34	35	36

<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]

i'#ve tried that and it comes back with N/A ... hmmm

edit: nvm, my bad..

 

[StefanVL]

Did you enter the formula with control + shift + enter?

 

[Raanan]

yeh i think the issue was with the format of the cells, usually happens when working with dates