+ Reply to Thread
Results 1 to 8 of 8

Searching for String in Multiple Cells, Returning different Values.

  1. 09-17-2016, 12:18 AM #1
    ShennyP
    ShennyP is offline
    Registered User
    Join Date
    09-17-2016
    Location
    Adelaide, Australia
    MS-Off Ver
    2013
    Posts
    6

    Searching for String in Multiple Cells, Returning different Values.

    Basically what I am looking to do is search for a string (name of a player) in 45 cells. If the string appears in one of 9 of these cells I need to return a 3. Then there is another 9 cells which need to be searched and if it is in there it needs to return a 2. Another 9 cells which need to be searched and if in there needs to return a 1. Finally there are 18 cells to be searched and if the string is contained it needs to return a P. The string will not appear in any more than 1 of the 45 cells and may not appear in any of them. If they appear in none I need it to return a 0 or just nothing. Unfortunately the cells I will be searching will contain the string but won't be an exact match as it has some random characters surrounding the name of the player. Therefore I think I am limited in how to go about this.

    I was thinking I would have to use an IF and search all 45 cells individually to get this to work but I am not sure how to go about it.

    Cheers if you can help.
    Reply With QuoteRegister To Reply
  2. 09-17-2016, 02:37 AM #2
    Crooza
    Crooza is offline
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Searching for String in Multiple Cells, Returning different Values.

    Can you post your spreadsheet so we can see how your data is arranged For example is the 45 cells in one column or multiple columns? Are each group of 9 in one column or multiple columns?



    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Happy with my advice? Click on the * reputation button below
    Reply With QuoteRegister To Reply
  3. 09-17-2016, 10:21 AM #3
    ShennyP
    ShennyP is offline
    Registered User
    Join Date
    09-17-2016
    Location
    Adelaide, Australia
    MS-Off Ver
    2013
    Posts
    6

    Re: Searching for String in Multiple Cells, Returning different Values.

    Quote Originally Posted by Crooza View Post
    Can you post your spreadsheet so we can see how your data is arranged For example is the 45 cells in one column or multiple columns? Are each group of 9 in one column or multiple columns?



    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    I have tried to upload it. So basically I have an "Overall" sheet where I will need each cell for each player to be searching that rounds data to see if they have a 3, 2, 1 or P for the round.I had to delete some of the round sheets because the file was too large.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 09-17-2016, 10:51 AM #4
    ShennyP
    ShennyP is offline
    Registered User
    Join Date
    09-17-2016
    Location
    Adelaide, Australia
    MS-Off Ver
    2013
    Posts
    6

    Re: Searching for String in Multiple Cells, Returning different Values.

    =IF(ISNUMBER(SEARCH(AK50,AG3)),"3",
    IF(ISNUMBER(SEARCH(AK50*​,AG4)),"2",
    IF(ISNUMBER(SEARCH(AK50*​,AG5)),"1",
    IF(ISNUMBER(SEARCH(AK50*​,AG6)),"P",
    IF(ISNUMBER(SEARCH(AK50*​,AG7)),"P")))))

    This is what I want to do but it doesn't allow it, obviously need to repeat that a few times but that's what I am looking for.
    Reply With QuoteRegister To Reply
  5. 09-17-2016, 11:12 PM #5
    ShennyP
    ShennyP is offline
    Registered User
    Join Date
    09-17-2016
    Location
    Adelaide, Australia
    MS-Off Ver
    2013
    Posts
    6

    Re: Searching for String in Multiple Cells, Returning different Values.

    not sure if you are allowed to bump posts but I'm going to D:
    Reply With QuoteRegister To Reply
  6. 09-17-2016, 11:50 PM #6
    Crooza
    Crooza is offline
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Searching for String in Multiple Cells, Returning different Values.

    Hi. Not sure why you felt the need to bump it after less than an hour of loading your sheet. I've looked at the sheet you uploaded and I have no idea what your expected result looks like.

    I assume you want something filled in in the 'Overall' sheet. Can you give at least one example and explain how you arrive at that. The first post that talks about checking 45 cells in groups of 9 and giving a score of 3,2,1 or P appears to have been done in some fashion on each of the round sheets. Are you now asking a different question to your post #1?
    Reply With QuoteRegister To Reply
  7. 09-18-2016, 12:07 AM #7
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Searching for String in Multiple Cells, Returning different Values.

    Hi Shenny,

    Find attached your workbook with a User Defined Function (UDF) that does what I think you want. It looks like this:
    Please Login or Register  to view this content.
    Shenny UDF.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  8. 09-18-2016, 03:26 AM #8
    Crooza
    Crooza is offline
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Searching for String in Multiple Cells, Returning different Values.

    If Marvin's outcome is what you want and you want to avoid the UDf then try this array formula in C5 and drag across and down

    =IFERROR(INDEX(INDIRECT("'"&C$4&"'!$AF$3:$Af$55"),MAX(IF(ISNUMBER(FIND($B5,INDIRECT("'"&C$4&"'!$AG$3:$AG$55"))),ROW($AG$3:$AG$55),0))-2),"")

    Array formulas need to be entered with Control + shift + Enter

    Edit - OR to use on your spreadsheet with column headings R1, R2 etc (and not Round 1 like in Marvin's version) then
    this

    =IFERROR(INDEX(INDIRECT("'"&SUBSTITUTE(C$4,"R","Round ")&"'!$AF$3:$Af$55"),MAX(IF(ISNUMBER(FIND($B5,INDIRECT("'"&SUBSTITUTE(C$4,"R","Round ")&"'!$Ag$3:$Ag$55"))),ROW($AG$3:$AG$55),0))-2),"")

    BUT you will need to change the sheet nae of Round1 to Round 1 - note the space to be consistent with the other sheets
    Last edited by Crooza; 09-18-2016 at 03:42 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Wildcard searching for multiple values and returning a result
    By thhil in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-27-2014, 02:32 PM
  2. Searching a column for a value and returning corresponding values on multiple sheets
    By idemdito in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-14-2011, 05:02 PM
  3. Searching for values within a string across a range of cells
    By futureboy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2010, 07:53 PM
  4. Searching for non specific text and returning multiple values
    By simmo86 in forum Excel General
    Replies: 3
    Last Post: 08-02-2010, 10:07 AM
  5. Excel 2007 : searching tables returning multiple cells
    By stephen1000 in forum Excel General
    Replies: 8
    Last Post: 01-19-2009, 05:09 PM
  6. Searching one value and returning multiple values in one cell
    By Ray789 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-27-2008, 04:31 PM
  7. Searching Row and returning multiple values
    By RichardCragg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2007, 07:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1