vlookup problem

[joshpark1]

Hi,

I have a list of items where column A has an invoice # and column B has item(s) matching with that invoice. I would like to do a VLOOKUP from another worksheet where I can type in the invoice # in one cell and have it spit out all the items that match that invoice #. Right now I can only get the formula to find the 1st item listed with the matching invoice # even if I repeat the formula a few times.

I'm working with Excel 2007 and Windows Vista. Any suggestions?

 

[jbeaucaire]

Is this a one at a time thing? You're inputting one invoice # and getting one list of items to do something with, then you work with another invoice #?

I prefer to avoid array formulas when I can, others will come along and suggest them...so I'll show you my technique. I add a "key" column to my data sheet that watches the Key item I'm searching for and flags the rows ON the data sheet for transfer. Here's a sample I've offered for others to see the technique:

http://www.excelforum.com/attachmen...et2-using-vlookup-nonarraymultiplelookup2.xls

On the SUMMARY sheet selecting apple flags 4 rows on Sheet1. Then a simple INDEX/MATCH formula can draw them over with no performance hit.

This technique will scale up and stay very quick. An array approach may reduce performance as your data set continues to grow. Worth considering.

 

[joshpark1]

that's interesting, I'll give it a try!

I add new invoices every day on a list style data entry log on one worksheet, then I manually move the data over to an invoice template to print so I have record of all the orders, but have a nice looking invoice to give to my customers.

 

[Devin]

In your VLOOKUP formula are you using "0" or "False"? Also, VLOOKUP doesn't like numbers as text. Have you checked to see if the invoice numbers in your data set are actually numbers and not text? If that is the case, convert them to text by using a double negative formula (=--##).

 

[joshpark1]

I tried the key column, but the formula I'm using turns up a VALUE! error. Does the key column need to be on the invoice too to work? Could you give me an example of how the index/match formula would look? Thank you!

 

[jbeaucaire]

One thing I forgot to mention, I used a NAMED range in the Key column formulas. On the sheet where I making my "apple" choice, I clicked on that cell, then types KEY into the NameBox. The NameBox is just to the left of the formula bar and usually shows you the name of the cell you've clicked on...click on A1 and it shows A1. Got it?

So, find your "invoice #" cell and name it KEY and then the formula in the Key Column will work as I designed them.