# VLOOKUP Problem in EXCEL 2010

Tom Hahn
Guest
Posts: n/a

 24th Feb 2012
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?

--

Claus Busch
Guest
Posts: n/a

 24th Feb 2012
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

Tom Hahn
Guest
Posts: n/a

 25th Feb 2012
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

--

Tom Hahn
Guest
Posts: n/a

 25th Feb 2012
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

--

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post rob11marmion@gmail.com Microsoft Access External Data 1 17th Feb 2013 11:04 PM rob11marmion@gmail.com Microsoft Excel Discussion 0 21st Dec 2012 06:22 AM Darrell Microsoft Excel Programming 5 12th Jan 2010 10:03 PM AvrahamC Microsoft Outlook BCM 0 4th Jan 2010 02:56 PM =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM

Features