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

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

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
---

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

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