selecting data from a list

[dave05]

Can't seem to figure this one out - date looks like this:

C1 A 1 xxx
C1 A 2 xxx
C1 B 1 xxx
C1 B 2 xxx
C1 C 1 xxx
C1 C 2 xxx
C2 A 1 xxx
C2 A 2 xxx
etc., you get the idea,

where "xxx" are random numbers (not necessarily the same, and don'
follow a pattern like the first 3 columns). I need to be able to pic
the number in the last column to display in a cell on a differen
worksheet, based on the criteria in the first 3 columns. HLOOKUP
VLOOKUP, INDEX, etc. do not allow for the retention of all the dat
needed to sort through. An autofilter command works nice to see th
data, and an easy macro can be wipped up to filter what is needed i
the first three columns, but I can't find a way to actually select
cell as a result of that.

The data file is approx. 4500 rows, with only 4 columns. Any ideas

 

[Biff]

Hi!

Not sure this is what you're looking for or not.

=INDEX(D1100,MATCH(1,(A1:A100="C2")*(B1:B100="A")*
(C1:C100=2),0))

Or even Sumproduct if the values in the 4th column are
unique to the criteria of the other 3 columns.

Biff

 

[Paul Corrado]

Dave, if the last column of data is a number you can use this formula.

=SUMPRODUCT((A1:A8="C1")*(B1:B8="A")*(C1:C8=2)*(D18))

Adjust ranges and change the criteria to cell references as needed