Table with VLOOKUP?

[Tatebana]

I have a relatively simple table which I want to use with a VLOOKUP Formula
(or similar). In column C, articles (text) will be entered from a table that
goes from column X4 to Z50; columns Y and Z contain the two prices in the
same line as the article (text) in col. X. Column C is indefinitely long.

Therefore, I tried to use the formula =IF(C4=0,0,VLOOKUP(C:C,$X$4:$Z$50,2))
and/or =IF(C4="","",VLOOKUP(C:C,$X$4:$Z$50,3)) respectively to retrieve the
prices in 2 columns (H and K). The list in column X is sorted alphabetically.
The entries in columns C should, however, be in any order as they are entered
one by one. (With validation to the list in X to avoid misspelling)

The formulae work well in the first eleven lines, but when I copy them
further down, only articles down to X27 get prices, those from X28 down do
not! What do I do wrong? Or is it maybe another formula?

 

[Dave F]

The first argument in a VLOOKUP statement is the lookup value. This is
either a cell reference or a static value, not an entire column. That may be
the first issue.

Dave

 

[Dave Peterson]

Actually, excel is pretty forgiving. =vlookup(c:c, ...) will work--if the rest
of the formula works. Excel will essentially translate that c:c to
C(whateverrowtheformulaison).

Personally, I find that kind of syntax distracting and I don't like using it.

 

[Dave Peterson]

If you're matching on text values, I would think you'd want an exact match.

I'd try:
=IF(C4=0,0,VLOOKUP(C:C,$X$4:$Z$50,2,False))

In fact, to address Dave F's point, I'd really use:
=IF(C4=0,0,VLOOKUP(c4,$X$4:$Z$50,2,False))

And I find putting the table on its own dedicated worksheet much easier to work
with. I don't have to worry about inserting complete rows/columns or deleting
rows or columns.

Debra Dalgleish has some very nice notes:
http://www.contextures.com/xlFunctions02.html

 

[Dave F]

That's odd. How does Excel know the value that you want to lookup, if the
value argument references an entire column?

Dave

 

[Dave Peterson]

Excel is very smart!

I have no idea how it knows. But if you enter it as an array formula, it'll do
something else, too.