Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table with VLOOKUP?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table with VLOOKUP?
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 -- A hinte to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Tatebana" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table with VLOOKUP?
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 F wrote: 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 -- A hinte to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Tatebana" wrote: 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 Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table with VLOOKUP?
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 Tatebana wrote: 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 Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table with VLOOKUP?
That's odd. How does Excel know the value that you want to lookup, if the
value argument references an entire column? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Dave Peterson" wrote: 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 F wrote: 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 -- A hinte to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Tatebana" wrote: 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 Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table with VLOOKUP?
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. Dave F wrote: That's odd. How does Excel know the value that you want to lookup, if the value argument references an entire column? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Dave Peterson" wrote: 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 F wrote: 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 -- A hinte to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Tatebana" wrote: 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 Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Size | Charts and Charting in Excel | |||
vlookup data in a dynamic pivot table? | Excel Worksheet Functions | |||
vlookup fails on data from a pivot table | Excel Worksheet Functions | |||
Table Array in VLOOKUP Relies on Data Validation | Excel Worksheet Functions | |||
How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) |