Return Matched Numeric Labels across Single Row

[Sam via OfficeKB.com]

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

 

[Max]

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(J250,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]

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,Sheet1!$A$17:$A$116,0))>=4),J250,"")))

 

[Sam via OfficeKB.com]

Hi Max,

Thank you very much for your time and assistance. That works Great!

Cheers,
Sam

 

[Max]

Great to hear that, Sam !
You're welcome.