I have a reference table (named SPOTS) that looks like this

Date GoldSpot Silver Spot
2/9/2012 $1,722.00 $33.59
2/21/2012 $1,753.00 $34.15
2/22/2012 $1,754.90 $34.19
2/23/2012 $1,780.00 $35.35
2/24/2012 $1,781.00 $35.41
12/31/2099

In a second table, I want to use VLOOKUP to find a match in Column A
and return either Column B or Column C on the row that contains the
match.

The second table (VALUES) looks like this (much simplified) after
update:

Date GoldSpot Quantity Value
2/9/2012 $1,722.00 3 $5166.00
2/21/2012 $1,753.00 5 $8765.00
2/22/2012 $1,754.90 1 $1754.90
2/23/2012 $1,780.00 4 $7120.00
2/24/2012 $1,781.00 2 $3562.00
12/31/2099

The logic is to retrieve the GoldSpot from SPOTS for the matching date
in Column A in VALUES and multiply it times Quantity to calculate the
Value column.

In C2, I placed the follwing formula:

=VLOOKUP(A2,SPOTS!A2:A7,2,FALSE)

This returns "#N/A"

However, if I change the formula to

=VLOOKUP(A2,SPOTS!A2:A7,1,FALSE)

This returns "40963" which is the numeric value for Date in SPOTS.

Why doesn't the first formula return the GoldSpot value for the
matching date?



--

Hi Tom,

Am 24 Feb 2012 18:10:47 GMT schrieb Tom Hahn:

> =VLOOKUP(A2,SPOTS!A2:A7,2,FALSE)
>
> This returns "#N/A"

try:
=VLOOKUP(A2,Spots!$A$2:$C$7,2,0)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Ron Rosenfeld wrote:

> On 24 Feb 2012 18:10:47 GMT, "Tom Hahn" <(E-Mail Removed)> wrote:
>
> > In C2, I placed the follwing formula:
> >
> > =VLOOKUP(A2,SPOTS!A2:A7,2,FALSE)
> >
> > This returns "#N/A"
> >
> > However, if I change the formula to
> >
> > =VLOOKUP(A2,SPOTS!A2:A7,1,FALSE)
> >
> > This returns "40963" which is the numeric value for Date in SPOTS.
> >
> > Why doesn't the first formula return the GoldSpot value for the
> > matching date?
>
> Because in your first formula, your table_array is only one column
> wide. VLOOKUP will not return data that is outside of the table
> array. If you want to return the value from the 2nd column, your
> table array must be at least two columns wide: eg:
>
> SPOTS!A2:B7 or even SPOTS!A2:C7
>
> And I would suggest using absolute addressing for your table array:
> SPOTS!$A$2:$C$Y so that if you drag the formula, the array
> reference won't change.

Thank you very much for the clear explanation, Your suggestion
satisfied my problem. I think I spent hours looking for the answer and
missed the obvious. (The Help system in Office 2010 is almost useless
and I haven't done any Excel work in a long time.)

Regards,
Tom

--

Claus Busch wrote:

> Hi Tom,
>
> Am 24 Feb 2012 18:10:47 GMT schrieb Tom Hahn:
>
> > =VLOOKUP(A2,SPOTS!A2:A7,2,FALSE)
> >
> > This returns "#N/A"
>
> try:
> =VLOOKUP(A2,Spots!$A$2:$C$7,2,0)
>
>
> Regards
> Claus Busch

Thank you. Your suggestion solved the problem on which I spent hours
looking for the answer (and over-looked the obvious.) I also replaced
the array specification with a Range.

Regards,
Tom Hahn

--