Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple conditions and multiple return values
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple conditions and multiple return values
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple conditions and multiple return values
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple conditions and multiple return values
Hi
"Minerva" wrote in message ... 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(Locatio ns!$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(Emplo yees!$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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
look up table values with multiple conditions | Excel Discussion (Misc queries) | |||
Cell values based upon multiple conditions | New Users to Excel | |||
How to calculate values in multiple values with multi conditions | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions |