Vlookup, Match, Wildcards, and Dates

[mrsmcm]

Vlookup, Match by Date with Wildcards!

Hi all, first time posting a new thread and hoping for any help!!

I'm trying to do a vlookup/match with wildcard, except that my lookup_value is a date, i.e. 12/18/2013, and my lookup_array is more in the form of "Cash Distribution - 12/18/2013" where the date would change with each distribution. Do I need to do anything specific because it's a date?

Formula: =VLOOKUP($C$7,'Raw data thru 6.30.17 (UTP)'!$B$1:$AA$107,MATCH("*"&'Distro Analysis by Investor'!C13&"*",'Raw data thru 6.30.17 (UTP)'!$G$1:$AA$1,0),FALSE)

VLOOKUP:
$C$7 = investor name
Range = lookup table, row 1 - Cash Distribution - 12/18/2013 (for example); column b, investor names

I know this first part works, as I have tested and confirmed so it has to be this second part.

MATCH: C13 = 12/18/2013
Raw data = G1 - G starts column dates start on - even if I switch this to B1, still doesn't work

Any help would be greatly appreciated.

~Mrsmcm​

 

[Jonmo1]

Can you define "Doesn't work" ?
Are you getting an error? What error?
Are you getting the wrong result? What result do you get? What result do you expect istead?

 

[steve the fish]

Date is just a number to excel that is formatted to look like a date. Your Cash Distribution - 12/18/2013 is however text so to get the MATCH function to find a match you need to do something like:

=MATCH("*"&TEXT(A1,"mm/dd/yyyy")&"*",A3:E3,0)

where A1 holds your date and A3:E3 holds your potential matches.

 

[Jonmo1]

So C13 is a DATE
But you're trying to match it against a text string containing that date, like Cash Distribution - 12/18/2013

The problem is that the date is really just a serial number, cell formatting makes it look like a date.

Try changing that match function to
MATCH("*"&TEXT('Distro Analysis by Investor'!C13,"mm/dd/yyyy")&"*",'Raw data thru 6.30.17 (UTP)'!$G$1:$AA$1,0)

 

[mrsmcm]

Jonmo, thanks for your response. The initial issue I was having, was the formula was returning #N/A. What I want to get is what the investors distribution was on that date, where I'm pulling from a large dump of data from another program.

 

[mrsmcm]

Steve, thanks for your response. I updated with your corrections and now it seems to be pulling the investor name in, not the distribution amount. The updated formula is below.

=VLOOKUP($C$7,'Raw data thru 6.30.17 (UTP)'!$B$1:$AA$107,MATCH("*"&TEXT(C13,"mm/dd/yyyy")&"*",'Raw data thru 6.30.17 (UTP)'!$G$1:$AA$1,0),FALSE)

C7 - investor name
Raw data - B1 - starts the investor names on the raw data tab
C13 - date of distribution

 

[Jonmo1]

Which column (Letter) are you wanting to return the value from?
I would think you probably want the match range to begin in column B

 

[steve the fish]

Your problem now appears to be your lookup range is $B$1:$AA$107 whilst your match range is $G$1:$AA$1. They should both start at B1 id imagine.

 

[mrsmcm]

Fantastic! Thank you both so much. My last question, which I didn't think of initially, is there a way to do an OR function with MATCH? My other dates don't necessary start with a 0, i.e. 5/24/2014, which then results in the formula not working unless I delete one of the m's.

 

[Jonmo1]

What about the day part of the string, will it also drop to only single digit?
July 4th would be 7/4/2017

?

If so just change mm/dd/yyyy to m/d/yyyy