Is VLOOKUP the appropriate formula for this?

[brunette]

Hello everyone,

I think I've been working on Excel formulas too much today and my brain is possibly switching off, as, for the life of me, I cannot get it to do what I want it to do!

Using Excel 2007, I have a workbook containing 2 sheets.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Reference</td><td style=";">Description</td><td style=";">Instock</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1234</td><td style=";">Item1</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5678</td><td style=";">Item2</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4321</td><td style=";">Item3</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">8765</td><td style=";">Item4</td><td style="text-align: right;;">25</td></tr></tbody></table><p style="width:5,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Inventory</p><br /><br />
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">OrderDate</td><td style=";">OrderQty</td><td style=";">Description</td><td style=";">Reference</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01-Jan</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">8765</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">01-Jan</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">5678</td></tr></tbody></table><p style="width:5,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">OrderForm</p><br /><br />
What I want is pretty simple (or so I thought it would be...)

I want to populate Cell C2 on OrderForm with the Description that matches the relevant reference number, which is somewhere in Column A, in Inventory.

I tried using a VLOOKUP formula but it errored out on me.

=VLOOKUP(D3;Inventory!A2:A5;3;FALSE)

Obviously, the example listed here is a very simplified version, as my inventory table has roughly 400ish rows of unique reference numbers and corresponding unique descriptions.

Clearly, either I'm missing something on the Vlookup formula, or I'm just getting it all wrong. Could you suggest the correct formula for me to do this?

Thanks in advance,
Bru

 

[VoG]

Try

=VLOOKUP(D3;Inventory!A$2:B$5;2;FALSE)

 

[TiaXL]

Hello everyone,

I think I've been working on Excel formulas too much today and my brain is possibly switching off, as, for the life of me, I cannot get it to do what I want it to do!

Using Excel 2007, I have a workbook containing 2 sheets.




<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Reference</TD><TD>Description</TD><TD>Instock</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1234</TD><TD>Item1</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">5678</TD><TD>Item2</TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">4321</TD><TD>Item3</TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">8765</TD><TD>Item4</TD><TD style="TEXT-ALIGN: right">25</TD></TR></TBODY></TABLE>

Inventory​

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>OrderDate</TD><TD>OrderQty</TD><TD>Description</TD><TD>Reference</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">01-Jan</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">8765</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">01-Jan</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">5678</TD></TR></TBODY></TABLE>

OrderForm​

What I want is pretty simple (or so I thought it would be...)

I want to populate Cell C2 on OrderForm with the Description that matches the relevant reference number, which is somewhere in Column A, in Inventory.

I tried using a VLOOKUP formula but it errored out on me.

=VLOOKUP(D3;Inventory!A2:A5;3;FALSE)

Obviously, the example listed here is a very simplified version, as my inventory table has roughly 400ish rows of unique reference numbers and corresponding unique descriptions.

Clearly, either I'm missing something on the Vlookup formula, or I'm just getting it all wrong. Could you suggest the correct formula for me to do this?

Thanks in advance,
Bru

correct formula sortof (its a comma not a but incorrect range, you are looking at only column A but trying to return the third column along

change your formula to

=vlookup(D3,Inventory!a:b,2,false)

if you use the whole coumn instead of a specific range i.e. a:b instead of a2:b5 then if yu add more data to the table you wont need to adjust your formula. However I have heard this causes problems in excel 2007 upwards due to the lack or real row limit.

 

[Yokiboha]

with vlookup, the first column must be sorted ascending...presumably it is?

 

[VoG]

with vlookup, the first column must be sorted ascending...presumably it is?

That's only if the fourth argument is set to TRUE.

 

[brunette]

Thank you all for the quick responses.

The first column was indeed sorted, and the problem was, in fact, in that I had the wrong range. Got it to work perfectly now. Thanks all very much again.