Matching two data sets

**sbarnhar** · 11-02-2009, 08:53 PM

Hello:

I am using the dates in C to match with A, then putting the value from B in E. I have checked that all the data are in proper form, ie, A and C dates and B and D numbers, but still get the NA where the Vlookup is. My Vlookup is VLOOKUP(C3:C5,A3:B13,2,0). Any help would be appreciated.

A B C D E
6/28/2002 4.27 06/28/2002 3.85 #N/A
7/1/2002 4.32 07/05/2002 3.82 #N/A
7/2/2002 4.30 07/12/2002 3.88 #N/A
7/3/2002 4.24
7/5/2002 4.11
7/8/2002 4.29
7/9/2002 4.38
7/10/2002 4.36
7/11/2002 4.30
7/12/2002 4.39
7/15/2002 4.35

**JBeaucaire** · 11-02-2009, 09:06 PM

The first parameter in a VLOOKUP is a single search criteria, not a range.

VLOOKUP(C3, $A$3:$B$13, 2, 0)

**DonkeyOte** · 11-03-2009, 03:50 AM

JB, the first parameter being a multi cell range is not the fundamental problem... if the same formula is applied to C3:C5 the first parameter will utilise the appropriate row as criteria (ie E3 will use C3, E4: C4 and E5: C5)

As I see it the most likely cause is incompatible data types between criteria (C3:C5) and source (A3:A13) but to know that we would need to see a file.

**sbarnhar** · 11-03-2009, 09:37 AM

Thank you for the responses.

Sorry, attached I hope is the file, note I have a couple different versions of the vlookup as I was trying things.

**DonkeyOte** · 11-03-2009, 09:44 AM

As suspected it's a data type compatibility issue.

The values in C are not dates whereas the values in A are... you can either re-enter dates in C in date form (ie typing in the date again) or alternatively you can coerce strings in C to dates by highlighting column C -> Data -> Text to Columns -> in Step 3 select MDY -> Finish

Once coerced you should find your get your results

**sbarnhar** · 11-03-2009, 09:56 AM

Thank you DonkeyOte, much appreciated, it worked like a charm.

However, I have one further question about this because this problem is imperceptable to me. How did you know the data were not the same format? I looked several times, re-formatted the cells to be dates and every time I looked at the 'date' columns they show as dates and the number columns show as numbers?

**DonkeyOte** · 11-03-2009, 10:01 AM

Formatting a cell does not alter the underlying data type.

Consider: A1 "Apple"

If I alter the cell format of A1 to be a Number "Apple" does not suddenly become a numeric value... the same then holds true of any text string.

How to test ?

Dates in Excel are Integers... so an ISNUMBER test will tell you if a value you think is a date is really a date -- if the ISNUMBER returns FALSE you know it is a text string.

How did I know ?

A hunch... generally speaking given the values appeared to exist in both data sets there were only two real possibilities for the #N/A, either

a) data types inconsistent

b) data values different -- ie one or other column also included a time value which via format was not visible

generally speaking a) is the more common issue.

**sbarnhar** · 11-03-2009, 11:13 AM

Thank you very much.

**JBeaucaire** · 11-03-2009, 12:31 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon to leave Reputation Feedback, it is appreciated)