scoobz wrote:
OK here goes:

In a data sheet (sheet 1) I have the following columns:

A: Qty
B: ItemType1
C: ItemType2
D: ItemType3

My end objective is to have a formula that will lookup the highest
quantity based on a given criteria of Item Types. (It should also
allow me to select the no2 position, no3 position etc...)

Through searching on line, I have managed to piece together the below
array formula, which gives me most things, but I would like to add a
coule more.

Formula is located on Sheet2 for example:

=IF(SUM(('Sheet1'!B2:B300=D3)*('Sheet1'!C2:C300=E 3))=1,LARGE(IF('Sheet1'!B2:B300=D3,IF('Sheet1'!C2 :C300=D3,'Sheet1'!A2:A300)),1),"")


Additions on the above array formula:

1. To include a third criteria (as the above only allows two to be
defined: in cells D3 and E3).

2. To include a flexible amount of rows to be included in the range.
As the rows of data will change, I need something that can compensate
for this. (I used a countif: =COUNTIF(B:B,"<" to retrieve the number
of rows with data, but I'm not sure how I can put this in the
formula. If take the total column (A:A) than I get an error in the
formula as the array has empty cells.

3 To have a seperate lookup formula, that I can use like a 'vlookup'
on the row in which the qty is matched. Example: The 2nd highest qty
matched is 500, so I would like it to look up across this and provide
me with the ItemTypes that it was matched to.

Any help is much appreciated!


I think you could use a SUMPRODUCT formula.

You would find very interesting this page from Bob Phillips site:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


If you need more help, it could be better if you could upload an example
file to www.savefile.com

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy