Vlookup match and multiple returns

[taikhint]

I have 111,222,333,444 in my sheet 1, column A. In sheet 2, column A has aaa111zzz,bbb222zzz,ccc333zzz, ddd444zzz,aaa111zzzff. And sheet 2, column B has 100,200,300,400 and 500 respectively. How do i lookup 111 inside sheet 2 column A and return multiple values? Thanks!

 

[mikerickson]

Put this CSE formula in a cell and drag down wards.

=LARGE((ISNUMBER(FIND("111", $A$1:$A$100))*$B$1:$B$100), ROW(A1))

This formula needs to be put in a single cell, entered with Ctrl-Shift-Enter (Cmd+Return for Mac), and then dragged down.

It will return the matching numbers from column B, sorted descending.

 

[anandvarma]

have look at the following formula

=VALUE(LEFT(REPLACE(Sheet2!A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},Sheet2!A1&"0123456789"))-1,""),3))

 

[taikhint]

Put this CSE formula in a cell and drag down wards.

=LARGE((ISNUMBER(FIND("111", $A$1:$A$100))*$B$1:$B$100), ROW(A1))

This formula needs to be put in a single cell, entered with Ctrl-Shift-Enter (Cmd+Return for Mac), and then dragged down.

It will return the matching numbers from column B, sorted descending.

Thanks!

 

[taikhint]

have look at the following formula

=VALUE(LEFT(REPLACE(Sheet2!A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},Sheet2!A1&"0123456789"))-1,""),3))

Thanks