Index match formula

[asad]

Hi All,

I have a simple Index match formula which is giving me a headache. It is one of the simplest of the formulas but can't get my head around it.

28/09/2018	26/09/2018	Wed
6/11/2018	27/09/2018
25/12/2018	28/09/2018
26/12/2018	29/09/2018
1/01/2019	30/09/2018
28/01/2019	1/10/2018
11/03/2019	2/10/2018
19/04/2019
22/04/2019
25/04/2019
10/06/2019
27/09/2019
5/11/2019
25/12/2019
26/12/2019

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I have tried this

{=INDEX(TEXT($B$1:$B$7,"ddd"),IF(ISNUMBER(MATCH($B$1:$B$7,$A$1:$A$15,0)),ROW($B$1:$B$7)))}

but it gives me Wed as the answer as is hown above. The answer is supposed to be Fri.

Thanks
Asad

 

[asad]

Got it,

=TEXT(SUMPRODUCT(--ISNUMBER(MATCH(B1:B7,A1:A15,0))*(B1:B7)),"ddd")

does the trick

 

[Aladin Akyurek]

Care to provide a non-formula description of the problem you wanted to solve?

 

[asad]

Sorry Aladin,

I should have explained it in my first post.
I am trying to check if any of the dates in column B matches any of the dates in column A and then get the day of that matching date as an outcome. There will be maximum one day that will be matching, but most of the times, there will be none. In the later case, I want the answer to be blank.

Thanks
Asad

 

[Aladin Akyurek]

Why 2018-06-09 is Fri as there is no exact match for this date in the column A range?

 

[asad]

The formula is not matching just one date. It is matching the whole range in column B against the range in column A. So the date in B 28/09/2018 is matching with the first date in column A and that day is a Friday. Hence, the desired answer is Fri.

 

[Aladin Akyurek]

The formula is not matching just one date. It is matching the whole range in column B against the range in column A. So the date in B 28/09/2018 is matching with the first date in column A and that day is a Friday. Hence, the desired answer is Fri.

Goal: The day name of any date which matches a date in column A.

=TEXT(LOOKUP(9.99999999999999E+307,MATCH(B1:B7,A1:A15,0),B1:B7),"ddd")

 

[asad]

Perfect. Yet again. Sir Aladin.
Thanks a lot.
Asad

 

[Aladin Akyurek]

Perfect. Yet again. Sir Aladin.
Thanks a lot.
Asad

You are welcome.