Thanks for the help, but the items under each of the 3 columns are duplicated
for both sheets...several entries of an item exist for each....how to
retrieve multiple values for a particular combination of the 3 column items?
I tried using getpivotdata() but it doesn't retrieve multiple values.
Any other idea?
Thanks

"Arvi Laanemets" wrote:

When on another sheet you have selected/entered all 3 conditions into
separate cells, and you want a list of matching values in 4th column, then:

1. In source sheet, add a column to left of your table (p.e. new column will
be A, Type is in column B, etc.). Into cell A2 enter the formula
=IF(AND(B2=TypeCond,C2=PositionCond,D2=PlaceCond), SUMPRODUCT(--(B$2:B2=TypeC
ond),--(C$2:C2=PositionCond),--(D$2:D2=PlaceCond)),"")
, where TypeCond, PositionCond and PlaceCond are references to conditions on
another sheet, or according named ranges.

2. Copy the formula down at least for same number of rows, as you have data
in your table. All rows matching conditions will be numbered (1, 2, etc.)
Hide column A.

On another sheet, use VLOOKUP to return rows from 1st sheet, which have in
column A values 1, 2, etc. Something like
=IF(ISERROR(VLOOKUP(NumExpr,FirstTable,5,0)),"",VL OOKUP(NumExpr,FirstTable,5
,0))
for first returned colum - for other columns you can simplify the formula
like this:
=IF(A3="","",VLOOKUP(NumExpr,FirstTable,6,0))

But when all what you need is to list all rows mathcing 3 criteria, then why
don't you simply use Autofilter. Set according filter for all 3 columns, and
you have it!


Arvi Laanemets


"Minerva" wrote in message
...
I am not sure whether to use IF/VLOOKUP for this case:-
There are 3 columns of data common to two sheets like this-
Type Position Place
type1 posn1 place1
type2 posn2 place2
..........and so on.........(a 1000 records)
I want to match records in sheet2 which have same type, position, place (3
conditions) as that of sheet1 and retrieve the 4th column in
sheet2..........wherein there are multiple results of the matched 3
conditions.
Is there any other function that I can use here or a nested vlookup?
Thank you for your help.