Hi guys,
I have values in the area A1:Y25.
In column Z, I have certain words:
BLUE
RED
WHITE
...
In AA1, I need a formula which will find a cell from A1:Y25 which contains the word BLUE, and return the whole value of that cell, for example "THE BEAUTIFUL BLUE DANUBE".
Any ideas? Thanks!
----------
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
Jacc, I guess you wanted to post something but the post is empty?
Try this:Formula:
Please Login or Register to view this content.
It is an array formula so you have to enter it with Ctrl + Shift + Enter
Thanks, in your file it works perfectly but in mine it doesn't
Is there something wrong here? Does it have to start in A1?
(I do enter it with Ctrl + Shift + Enter.)
=INDEX(IY7:JA9;MAX(IF(ISNUMBER(SEARCH(JX8;IY7:JA9));ROW(IY7:JA9)));MAX(IF(ISNUMBER(SEARCH(JX8;IY7:JA9));COLUMN(IY7:JA9))))
There was indeed a bug.The previous version only worked when the range started in A1. This will work anywhere:
Formula:Please Login or Register to view this content.
Hm this didn't work either :/
But I solved it this way (because the range is always fixed, but it doesn't start in A1):
=INDEX(IY7:JW31;MAX(IF(ISNUMBER(SEARCH(JX8;IY7:JW31));ROW(IY7:JW31)))-6;MAX(IF(ISNUMBER(SEARCH(JX8;IY7:JW31));COLUMN(IY7:JW31)))-258)
Thanks anyway!
Glad to hear it works, sort of.
I missed this post but when you say it does not work I have to test. Maybe someone else tries to get something out of this thread in the future. It certainly works in this workbook, the datarange can be moved around as (not) needed.
By they way, don't forget to mark the thread SOLVED if you are satisfied with the results.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks