some questions on Lookup & Reference formulas

[Pete Wright]

I have some questions regarding Lookup & Reference formulas.
Since there will most likely be more then one question, I will keep them all together in this single topic.

Questions:

1. A simplified example:
   There is a date in Sheet1 Cell A5 > DATE(2020,1,1)
   and there is a date converted to text in Sheet2 Cell B2 > TEXT(DATE(2020,1,1),"dd.mm.yyyy") within the range A2:B4 which has the Name MyList
   
   Why is MATCH(TEXT(A5,"dd.mm.yyyy"),MyList,0) returning #N/A while EXACT(TEXT(A5,"dd.mm.yyyy"),Sheet2!B2) returns TRUE
   
   See uploaded file (Chandoo_Question_LookupAndReference_001.xlsx)

 

[rahulshewale1]

hi @Pete Wright ,


C5: Copy down-
=IFERROR(INDEX(holidays!$A$2:$A$5,MATCH(TEXT(A5,"dd.mm.yyyy"),holidays!$B$2:$B$5,0)),)

 

[Pete Wright]

C5: Copy down-
=IFERROR(INDEX(holidays!$A$2:$A$5,MATCH(TEXT(A5,"dd.mm.yyyy"),holidays!$B$2:$B$5,0)),)

Thanks, but I would really like to understand why my formulas are not working.
So, what is wrong with them?

 

[vletm]

Pete Wright
Functions works ... if use those as those could work.
Have You checked eg Match's syntax?

Have You tried to use whole date in cell A5 ... and show its value as You want?

 

[Khalid NGO]

Hi Pete,

As vletm pointed out about the Match Function Syntax, you also need to adjust the range holiday_list to one column, so that the Match function works properly, and the best rule for working with dates is "Always Enter Proper (Number Formatted) Dates".

Regards,

 

[pecoflyer]

And be aware the IFERROR function is dangerous. It hides ALL errors, which is not always what is needed...

 

[Pete Wright]

Many thanks to you all!

I was blind, but now I can see.

It was all about the syntax. It's a pity that the Office Help does not explicitly point out the data types needed. I had to guess, so I thought the range can handle more then one column.

Have You checked eg Match's syntax?

As vletm pointed out about the Match Function Syntax, you also need to adjust the range holiday_list to one column, so that the Match function works properly...

MATCH function @ Office.com
lookup_array Required. The range of cells being searched.

When I see "range", I think of multidimensional data. My mistake. It seems that the range parameter can only be one dimensional.

... and the best rule for working with dates is "Always Enter Proper (Number Formatted) Dates".

I know, but since I'm used to the German date format, it's easier for me to handle this way.

And be aware the IFERROR function is dangerous. It hides ALL errors, which is not always what is needed...

Alright, I don't use IFERROR anyway because of this.

Now, question 1. is answered. Thanks again.

 

[vletm]

Pete Wright
If someone has challenges eg with MATCH-function then, please, check its syntax:
I can find at once eg:
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
and
many times there are clear examples too.

Dates
If You're using Dates, then keep those as Dates.
You tried to change those as Text.