• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to change formula

@Belleke , are you on version 365?

Maybe this?
=INDEX($A$2:$A$7;AGGREGATE(15;6;(ROW($E$2:$E$7)-1)*(SEARCH($E$2:$E$7;E11)^0);1))

But something weird happens for the second example. I have 2 hits in the list. I did not anticipate it and I need to go now.
 

Attachments

  • Formules testen.xlsx
    12.2 KB · Views: 9
Last edited:
Never good to be in a hurry, but C/OV gekocht includes the string OV gekocht. That's why I found 2 results.
Using small works in the current set-up, but there might be other cases?
 
GraH - Guido said:
@Belleke , are you on version 365?

Maybe this?
=INDEX($A$2:$A$7;AGGREGATE(15;6;(ROW($E$2:$E$7)-1)*(SEARCH($E$2:$E$7;E11)^0);1))

But something weird happens for the second example. I have 2 hits in the list. I did not anticipate it and I need to go now.
Click to expand...
1] Your formula:
=INDEX($A$2:$A$7;AGGREGATE(15;6;(ROW($E$2:$E$7)-1)*(SEARCH($E$2:$E$7;E11)^0);1))

2] Better using "/" instead of "*"+"^0"
Formula result giving same and a bit shorter

=INDEX($A$2:$A$7;AGGREGATE(15;6;ROW($E$2:$E$7)-1/SEARCH($E$2:$E$7;E11);1))

Regards
 
GraH - Guido said:
@Belleke , are you on version 365?

Maybe this?
=INDEX($A$2:$A$7;AGGREGATE(15;6;(ROW($E$2:$E$7)-1)*(SEARCH($E$2:$E$7;E11)^0);1))

But something weird happens for the second example. I have 2 hits in the list. I did not anticipate it and I need to go now.
Click to expand...
1] It is the original lookup range:

1709667557020.png

2] If the row 3 and row 4 exchange the order, and become>>

1709667682470.png
Then, your above formula will give wrong result

Regards
Bosco
 
You must log in or register to reply here.
Back
Top