Index Match, Left Question

[Click22]

Hi Guys,

I am looking to do a simple index match formula but I am getting stuck on how to get the qty come through.

I currently have this:

=INDEX('Sheet 2'!N:N,MATCH(C27,'Sheet 2'!C:C,0))

But I require it to ignore the (195) on the end.

Sheet 1:
Cell C27 - 24/05/21
Cell C32 - Formula in here

Sheet 2:
Column C:C - 24/05/21 (195)
Column N:N - 140

I have tried nesting a LEFT formula within it, but not with any success.

Any help would be great.

Thank you

 

[Fluff]

Is the date in C27 a real date or text?

 

[Click22]

Hi Fluff,

C27 is a formulated date using the workday function.

 

[Fluff]

In that case try

=INDEX(Sheet2!N:N,MATCH(text(C27,"dd/mm/yy")&"*",Sheet2!C:C,0))

 

[Click22]

I have just tried but returns #N/A

 

[Fluff]

In that case what exactly is in col C on sheet2?

 

[Click22]

Here is a screenshot if that helps. Col C is week commencing dates.

 

[Fluff]

Those dates are not consistent some have a 2 digit year & one has a 4 digit year. They will need to be one or the other.
Also you seem to have merged cells which may cause a problem.

 

[Click22]

Thanks Fluff, I hadn't noticed that.
All dates now put to dd/mm/yy format and all is working as your formula above.

Thank you very much

 

[Fluff]

You're welcome & thanks for the feedback.