MATCH returning #N/A by mistake

[AVO051]

Hi, the following formula is returning an #N/A error, but when I replace the offset formula by a cell range - which is automatically generated using the same formula - the match returns 1. I cannot understand why this is happening, especially since I have a similar formula in another sheet that works perfectly well.

=MATCH(Kalender_KamerRef;OFFSET(Lijst_Kamers;;MATCH(Legende!$F$5;OFFSET(Lijst_Kamers;-1;;1);0)-1;;1)) = #N/A error
=OFFSET(Lijst_Kamers;;MATCH(Legende!$F$5;OFFSET(Lijst_Kamers;-1;;1);0)-1;;1) = {0;0;0;4}
=MATCH(Kalender_KamerRef;{0;0;0;4};0) = 1

How can I correct this? Thanks for your input!

 

[Alex Blakenburg]

I can only see 1 difference between the 2 ways of doing it and that shouldn't cause your issue.
(your broken down formula has ";0" on the end which the main formula does not - but that should be the default anyway)

What is the:
• The value of Kalender_KamerRef
• The value of Legende!$F$5
• the range Lijst_Kamers - preferably also provide an XL2BB sample of this table.
Since you have MS365, I am surprised you need to use Offset at all.

 

[AVO051]

Thanks Alex! I found the following workaround that works:
MATCH(Kalender_KamerRef;IF({1};OFFSET(Lijst_Kamers;;MATCH(Legende!$F$5;OFFSET(Lijst_Kamers;-1;;1);0)-1;;1));0)

The ";0" was always there, but somehow got lost during copy/paste.

PS: This is an older Excel file (prior to MS365) that I copied from a previous project, hence the offset