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!


For Dynamic Ranges, you can see this page from Chip Pearson's site:

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy