Fetch cell value on basis of Find string in cell and Serial no matched

[shakaib26]

Hi,

Can anyone correct this formula to show the accurate cell value. Currently it is showing wrong result.

I want to fetch cell value on basis of finding string in cell and serial no matched.

Formula: =INDEX($B$2:$B$165,XLOOKUP(E2,$A$2:$A$165,IF(ISNUMBER(MATCH("OWS",$B$2:$B$165)),ROW($B$2:$B$165),""),""))


Thanks.

 

[Peter_SSs]

Is this what you mean?

=FILTER(B2:B165,(A2:A165=E2)*ISNUMBER(FIND("OWS",B2:B165)),"")

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[shakaib26]

Is this what you mean?

=FILTER(B2:B165,(A2:A165=E2)*ISNUMBER(FIND("OWS",B2:B165)),"")

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Thanks for your prompt reply, but this filter function bring nothing (blank).