Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Matched Numeric Labels across Single Row
Hi All,
Happy New Year. I would like a Formula to provide a solution to the scenario below returning the expected results. I have two Worksheets, Sheet1 and Sheet2 within the same Workbook using different data layouts. Sheet1 contains two columns of data; Column "A" = Numeric Labels (100-199) spanning A17:A116 and Column "B" = Numeric Values (0-10) spanning B17:B116. Sheet2 contains a row of Numeric Labels (1-100) spanning J250:AC250. Sample Data Layout: Sheet1 Col "A" Col "B" Labels Values 146 7 131 6 120 6 142 5 105 4 104 4 149 3 140 3 124 3 122 3 etc Sample Data Layout: Sheet2 (dashes to keep alignment) ---------------------Col "J" Col "K" Col "L" Col "M" Col "N" Col "O" etc Col "AC" RowNo.250----105-------122-------124-------140--------142-------146----------- ---149 Scenario: On Sheet1 find Numeric Values =4 (greater than or equal to 4) and Return their Numeric Label across a single row IF it MATCHES a Numeric Label on Sheet2. Expected Results: Numeric Labels returned across a single row 105, 142, 146 On Sheet1 Numeric Label 105 has a Numeric Value of 4 AND is also on Sheet2. On Sheet1 Numeric Label 142 has a Numeric Value of 5 AND is also on Sheet2. On Sheet1 Numeric Label 146 has a Numeric Value of 7 AND is also on Sheet2. Thanks, Sam -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Matched Numeric Labels across Single Row
One interp ..
In Sheet2, Presuming you have listed across in J250:AC250 : 105, 122, 124, etc Then perhaps this might return the expected results that you're after In J251: =IF(J250="","",IF(AND(ISNUMBER(MATCH(J250,Sheet1!$ A$17:$A$116,0)),INDEX(Sheet1!$B$17:$B$116,MATCH(J2 50,Sheet1!$A$17:$A$116,0))=4),J250,"")) Copy J251 across to AC251 For the sample data posted, you'd get: in J251: 105, in K251:M251: "blanks", in N251: 142, in O251: 146, and so on ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sam via OfficeKB.com" wrote: Hi All, Happy New Year. I would like a Formula to provide a solution to the scenario below returning the expected results. I have two Worksheets, Sheet1 and Sheet2 within the same Workbook using different data layouts. Sheet1 contains two columns of data; Column "A" = Numeric Labels (100-199) spanning A17:A116 and Column "B" = Numeric Values (0-10) spanning B17:B116. Sheet2 contains a row of Numeric Labels (1-100) spanning J250:AC250. Sample Data Layout: Sheet1 Col "A" Col "B" Labels Values 146 7 131 6 120 6 142 5 105 4 104 4 149 3 140 3 124 3 122 3 etc Sample Data Layout: Sheet2 (dashes to keep alignment) ---------------------Col "J" Col "K" Col "L" Col "M" Col "N" Col "O" etc Col "AC" RowNo.250----105-------122-------124-------140--------142-------146----------- ---149 Scenario: On Sheet1 find Numeric Values =4 (greater than or equal to 4) and Return their Numeric Label across a single row IF it MATCHES a Numeric Label on Sheet2. Expected Results: Numeric Labels returned across a single row 105, 142, 146 On Sheet1 Numeric Label 105 has a Numeric Value of 4 AND is also on Sheet2. On Sheet1 Numeric Label 142 has a Numeric Value of 5 AND is also on Sheet2. On Sheet1 Numeric Label 146 has a Numeric Value of 7 AND is also on Sheet2. Thanks, Sam -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Matched Numeric Labels across Single Row
Perhaps neater with an additional ISNA error trap:
In J251, copied across: =IF(J250="","",IF(ISNA(MATCH(J250,Sheet1!$A$17:$A$ 116,0)),"",IF(AND(ISNUMBER(MATCH(J250,Sheet1!$A$17 :$A$116,0)),INDEX(Sheet1!$B$17:$B$116,MATCH(J250,S heet1!$A$17:$A$116,0))=4),J250,""))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Matched Numeric Labels across Single Row
Hi Max,
Thank you very much for your time and assistance. That works Great! Cheers, Sam Max wrote: Perhaps neater with an additional ISNA error trap: In J251, copied across: =IF(J250="","",IF(ISNA(MATCH(J250,Sheet1!$A$17:$A $116,0)),"",IF(AND(ISNUMBER(MATCH(J250,Sheet1!$A$1 7:$A$116,0)),INDEX(Sheet1!$B$17:$B$116,MATCH(J250, Sheet1!$A$17:$A$116,0))=4),J250,""))) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Matched Numeric Labels across Single Row
Great to hear that, Sam !
You're welcome. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6bd6851d20ed3@uwe... Hi Max, Thank you very much for your time and assistance. That works Great! Cheers, Sam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Multiple instances of Single Criterion in Row & Return To a Single Col | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
AVERAGE Row of Numbers and Return Corresponding Numeric Label | Excel Worksheet Functions | |||
Return Single Instance of Numeric Values from a Column | Excel Worksheet Functions |