VLOOKUP Problem in EXCEL 2010

[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?



--

 

[Claus Busch]

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

 

[Ron Rosenfeld]

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.

 

[Tom Hahn]

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]

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

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

--