SEARCH Excel function multiple results

Asked Dec 07 '16 at 19:12

Active Apr 28 '21 at 20:26

Viewed 64 times

I'm trying to use the SEARCH function in Excel in order to find all the times that a word appear.

I'm using right now this:

=SI.ERROR(SI.ERROR(BUSCAR(D2466;Sheet1!P:P;Sheet1!B:B);
BUSCAR(D2466;Sheet1!T:T;Sheet1!B:B)); "NO")

    Not Sure if this is in English version:
=IFERROR(IFERROR(SEARCH(D2466;Sheet1!P:P;Sheet1!B:B);
SEARCH(D2466;Sheet1!T:T;Sheet1!B:B)); "NO")

`sheet1`
    A       B       C
1 Basic   Case1   Return1
2 Basic   Case1   Return2
3 Basic   Case1   Return3

`sheet2`
    A       B       C
1 find1   Case1    =FUNCTION
2 find2   Case2    =FUNCTION
3 find3   Case3    =FUNCTION

    =IFERROR(IFERROR(SEARCH(B1;sheet1!B:B;sheet1!C:C);
    SEARCH(B1;sheet1!A:A;sheet1!B:B)); "NO")
(SEARCH for word Case1 and for each match, return the value in C column)
Only appear the value: Return1

Which works, but only returns one row, and I have to record all.

edited Apr 28 '21 at 20:26

Jason Aller

3,541
28
38
38

asked Dec 07 '16 at 19:12

user3404207

Can you give sample data and output? Sounds like you need an array formula... – MacroMarc Dec 07 '16 at 19:17
Already edited, thank you. – user3404207 Dec 07 '16 at 19:37
If your data is **always** sorted on column B you can use a helper column on sheet 1 and a vlookup on sheet 2. Or if you have Office 365 Excel it can be done with one array formula. Otherwise it will take vba. – Scott Craner Dec 07 '16 at 19:45
@ScottCraner can you please give me a example on vba on this data? – user3404207 Dec 09 '16 at 13:43
There are many examples on this site as well as others. here is one: http://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell – Scott Craner Dec 09 '16 at 14:09

SEARCH Excel function multiple results

0 Answers0