Multiple conditions and multiple return values

[Minerva]

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.

[Arvi Laanemets]

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)),"",VLOOKUP(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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Minerva]

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)),"",VLOOKUP(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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[Arvi Laanemets]

Hi



"Minerva" <[email protected]> wrote in message
news:[email protected]...
> 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

But I just explained how to get 'multiple values for a particular
combination of the 3 column items'!

Here is a copy from my almost a year-old answer to some similar question (it
was for single condition only). Try to create a workbook following those
instructions - maybe it helps you to catch the logic. On sheet
EmployeesByLocation is created a list of all employees in certain location
from table Employees.

****
Let's assume you have a sheet Employees, with an employee list on it. Let
the table structure to be:
XXX, EmployeeCode, FirstName, LastName, Location, ...
At least one column in table must be a key column - and always filled when
there are any data in row. I assume it is EmployeeCode (column B).
About column XXX we'll speak later.

It'll be clever to have a sheet Locations, where all locations are listed.
It can be a single-column list Location, or a table containing additional
info with coluimn Location as leftmost. Define the column Location as
dynamic named range Locations
Locations=OFFSET(Locations!$A$1,1,,COUNTIF(Locations!$A:$A,"<>")-1,1)
(I assume the 1st row is header row).

Now on Employees sheet, you can format the Location column as data
validation list with Source=Locations.

Add 3rd sheet - p.e. EmployeesByLocations. Into cell A1 enter "Location:".
Format the cell B1 using data validation list with Source=Locations. Define
the cell B1 as named range p.e. Location
Location=EmployeesByLocations!$B$1

On sheet Employees, into 2nd row of column XXX (A2) enter the formula
=IF(B2=Location,COUNTIF(B$2:B2,Location),"")
and copy it down at least for all rows with employees (but you can have
spare rows at bottom). You get numbered all rows with same location as
selected on 3rd sheet.

Create a dynamic named range EmployeeTbl
EmployeeTbl=OFFSET(Employees!$A$1,1,,COUNTIF(Employees!$B:$B,"<>")-1,#)
where # is the number of columns in Employees table. You can hide the column
XXX now.

On sheet EmployeesByLocations, into row 3 enter headers:
EmployeeCode, FirstName, LastName, ...
NB! You don't need the column Location here anymore!

Into A4 enter the formula
=IF(ISERROR(VLOOKUP(ROW()-3,EmployeeTbl,2,0)),"",VLOOKUP(ROW()-3,EmployeeTbl
,2,0))
Into B4 enter the formula
=IF(A4="","",VLOOKUP(ROW()-3,EmployeeTbl,3,0))
Into C4 enter the formula
=IF(A4="","",VLOOKUP(ROW()-3,EmployeeTbl,4,0))
Into D4 enter the formula (when there were columns after Location in
Employees table)
=IF(A4="","",VLOOKUP(ROW()-3,EmployeeTbl,6,0))
etc.

Select cells with formulas in row 4, and copy formulas dows for as much rows
as you think you need. It's all. Select any location, and you get the list
of employees in this location.


Arvi Laanemets