+ Reply to Thread
Results 1 to 6 of 6

Find a specific value then return a value under its heading

  1. #1
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Smile Find a specific value then return a value under its heading

    Hi All,

    This is my 1st post and its a very simple request. I did try and search other posts but so many of them it's killing my eyes.

    This is what I want:-

    I have a heading in the form of a row of numbers from 1 to 10 in each cell. Under each of these numbers it will show another numbers. I call this set, Found numbers.

    Example:
    Heading 1 2 3 4 5 6 7 8 9 10
    Found numbers 5 2 0 2 6 4 0 2 1 5

    I then need a function that looks at Found numbers 0 and just show me the related heading number. So in this case, Found numbers 0 is under the heading 3 and also 7. So it will just show 3 7 in my designated cells. Likewise, Found 2 will show 2 4 8

    I hope you understand that, I know its sounds like a total novice explaining it, I am.

    Thanks.

  2. #2
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Find a specific value then return a value under its heading

    Hi HelpHelpHelp

    Assuming your data is in A1:J2 7 In say A3 and copy across try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Array formula CTRL + SHIFT + ENTER

    Edit: You can use a cell for your lookup value, ensure it is abolute.
    Last edited by Kevin UK; 12-14-2012 at 08:06 AM.

  3. #3
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Find a specific value then return a value under its heading

    Thanks for the swift reply.

    I have tried it and it doesn't seem to be happening.
    You can assume data headings is A1:J1. Found numbers in A2:J2 BUT my result cannot be in A3 because it only holds 1 cell. The result has to be away from this area. The found numbers can have duplicate as shown in my 1st post. The result will have to be in, say: K1 with the headings transpose. So K1 heading is 0, K2 is 1, K3 is 2 etc. Right of K1 will show 3 and 7 based on my example in 1st post. Hope that explains it. Thx again.

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Find a specific value then return a value under its heading

    You can put it where ever you wish. just adjust the cell references. Ensure when you enter the formula you use on the key board "CTRL + SHIFT + ENTER". "NOT" just "ENTER".

    Kevin

  5. #5
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Find a specific value then return a value under its heading

    Sorry, can you have I use this CTRL+....correctly? I have copied the formula, then find a cell where I want the formula to be in, it appears on the function bar, I then press CTRL+SHIFT+ENTER. It this correct?

    I kept the cell reference in your formula just to test and it does show a number 1 in those that has a zero. It should say 3 because 1st zero found was under the heading number 3.

    Help again plz.

  6. #6
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Find a specific value then return a value under its heading

    Hi HelpHelpHelp

    Have a look at the attached. I have used cell A8 as the lookup.

    Kevin
    Last edited by Kevin UK; 12-14-2012 at 09:52 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1