VLOOKUP Problem in EXCEL 2010

  • Thread starter Thread starter Tom Hahn
  • Start date Start date
T

Tom Hahn

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"
Click to expand...

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


Regards
Claus Busch
 
Ron said:
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.
Click to expand...

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 said:
Hi Tom,

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


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


Regards
Claus Busch
Click to expand...

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

--
 
Back
Top