match with date and 2nd option

Raanan

Board Regular
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.

 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"

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hello there,

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

previous poster got there first.

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

Regards

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?

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

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

Last edited:
Did you enter the formula with control + shift + enter?

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

Replies
2
Views
596
Replies
19
Views
862
Replies
4
Views
176
Replies
9
Views
468
Replies
1
Views
99

1,217,448
Messages
6,136,686
Members
450,024
Latest member
Beagle263

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.

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

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