PDA

View Full Version : VLOOKUP/database



drums4monty
03-01-2008, 07:50 AM
I have a database of items and for example part of this database are shirts, Mens reception shirt, L/S, Black, and they are in sizes XL,L, M, S, and then Price, all as different entries. I can use VLOOKUP to find a single occurance of the shirt but is it possible to get all occrances of the item as the prices are different. For example, is there a way to ask for Mens reception shirt, L/S, Black, M?

Regards

Alan

Bob Phillips
03-01-2008, 08:23 AM
=INDEX(D2:D20,MATCH(1,(A2:A20="Mens reception shirt")*(B2:B20="M")*(C2:C20="Black"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.

When editing the formula, it must again be array-entered.

Note that array formulae do not work with complete columns in Excel pre-XL2007, you have to specify a range.

drums4monty
03-01-2008, 08:49 AM
Thanks xld but I think I have asked the wrong question, I want all the occurances of the black shirt regarless of size so the 'M' bit should not be there.

By the way I am on Version 2002.