another vlookup with multiple results post

[AceFI]

hi team,
i know this has been done to death, however trying to match multiple results, using a date variable...

Raw Data: (sheet name)

A	B	C	D	E	F	G	H	I
Customer Number	var1	Var2	var3	Var4	Var5	Var6	Create Date	Var7
1							1/2/21
2							2/2/21
3							1/2/21
4							2/2/21
5							2/3/21
6							2/3/21
7							1/2/21
8							5/2/21

ACTUAL DATA (sheet name)

my formula before i put it in an array is as follows;
=INDEX('Raw Data'!$A$2:$A$500, SMALL(IF((Day2Date='Raw Data'!$H$2:$H$500), MATCH(ROW('Raw Data'!$H$2:$H$500), ROW('Raw Data'!$H$2:$H$500)), ""),ROWS('Raw Data'!$A$1:A1)))

i'm getting the #NUM! error and can't really see where i went wrong?

TIA

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

@AceFI
From your sample it looks like your dates in column H are text and I guess you are trying to look up a 'real' date (number) so try this modification to your formula.

=INDEX('Raw Data'!$A$2:$A$500, SMALL(IF((Day2Date='Raw Data'!$H$2:$H$500+0), MATCH(ROW('Raw Data'!$H$2:$H$500), ROW('Raw Data'!$H$2:$H$500)), ""),ROWS('Raw Data'!$A$1:A1)))

 

[AceFI]

@AceFI
From your sample it looks like your dates in column H are text and I guess you are trying to look up a 'real' date (number) so try this modification to your formula.

=INDEX('Raw Data'!$A$2:$A$500, SMALL(IF((Day2Date='Raw Data'!$H$2:$H$500+0), MATCH(ROW('Raw Data'!$H$2:$H$500), ROW('Raw Data'!$H$2:$H$500)), ""),ROWS('Raw Data'!$A$1:A1)))

thanks Peter - they are date formatted not text? (sorry.. i should have said that)

 

[snjpverma]

Ace, did you try Peter's version? What was the result?

 

[Peter_SSs]

@AceFI

As well as addressing snjpverma's questions, can you please also address my request to update your account details. If we do not know what Excel version you are doing then we do not know what functions or other resources you have that may assist in getting you the result you need.

Can you also please give us details about the named range Day2Date, including what value it holds and whether it is text or numeric?

 

[AceFI]

thanks all. still #num! result.
Looking back at the raw sheet, i've mad an error in the format of the 'create date'. its "2021-04-28 19:05" however, if i change the format to DD/MM/YY it changes fine (so its recognising its a date)
One thing / point i think i'm missing is referencing the 'day' portion of 'day2date' and 'day' in column H's data retrieval.

I'm designing this on Excel for mac 16.52.10 however it will be used within o365 online mainly

 

[Peter_SSs]

I'm designing this on Excel for mac 16.52.10 however it will be used within o365 online mainly

So, put this information in your profile so that it shows up like this so is always available to helpers. You can enter multiple version & multiple platforms if required.

format of the 'create date'. its "2021-04-28 19:05" however, if i change the format to DD/MM/YY

Changing the format changes the appearance but not the underlying value so even though you have made that format change, the cells will still contain the time component and that is very likely why you are getting #NUM!

One thing / point i think i'm missing is referencing the 'day' portion of 'day2date' and 'day' in column H's data retrieval.

That might need some clarification too. Don't forget my previous request about that.

Can you also please give us details about the named range Day2Date, including what value it holds and whether it is text or numeric?

In the interim, you could try
=INDEX('Raw Data'!$A$2:$A$500, SMALL(IF((Day2Date=INT('Raw Data'!$H$2:$H$500)), MATCH(ROW('Raw Data'!$H$2:$H$500), ROW('Raw Data'!$H$2:$H$500)), ""),ROWS('Raw Data'!$A$1:A1)))