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