if/ match formula

[jasman]

Hi. i have the below code which looks at two worksheets with data on, and if column B on sheet4 matches column B on supplierPartListSearch then it pulls through data in column A on sheet4.

sheet 4 has 1657 rows and supplierPartListSearch has 4135. The problem is that the formula only works for the first 1657 rows ?



=IF(ISNUMBER(MATCH("*"&Sheet4!B2&"*",supplierPartListSearch!$B$2:$B$4135,0)),Sheet4!$A$2:$A$4135)

 

[jasman]

just realised the results that formulae did give were wrong anyway. so ive tried the below but it doesnt return no results?

=IF(supplierPartListSearch!$B$2=Sheet4!B2:B1657,Sheet4!$A$2:$A$1657,0)

 

[T. Valko]

just realised the results that formulae did give were wrong anyway. so ive tried the below but it doesnt return no results?

=IF(supplierPartListSearch!$B$2=Sheet4!B2:B1657,Sheet4!$A$2:$A$1657,0)

See if this does what you want...

=INDEX(Sheet4!A$2:A$1657,MATCH(supplierPartListSearch!B2,Sheet4!B$2:B$1657,0))

 

[jasman]

gives me N/A#

 

[jasman]

-