+ Reply to Thread
Results 1 to 17 of 17

Automated value finder for a table help!

  1. 06-23-2015, 01:45 PM #1
    Joseph96
    Joseph96 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Automated value finder for a table help!

    Hi there,

    I have a table that along the top it has values, and along the side I also have values I would like to create a worksheet that will automatically find the value of two pieces of data one from each side of the table.

    so for example: Excel.PNG

    We have a simple multiplication table.

    if for example i put in for the data the numbers 6 and 5 i want the excel worksheet to automatically find the value that is found in the cell at which they both meet.

    I have tried before asking so know i am posting on this forum, i hope you guys understand and can help me.

    Thanks Joseph
    Reply With QuoteRegister To Reply
  2. 06-23-2015, 01:57 PM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Automated value finder for a table help!

    I can't read the PNG you added.

    You get better help if you add a small excel file, without confidential information.

    Please also add manualy, the expected result in your file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 06-23-2015, 02:08 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Automated value finder for a table help!

    Perhaps something like this...

    A
    B
    C
    D
    E
    F
    G
    1
    First
    2
    Second
    1
    2
    3
    4
    5
    3
    1
    1
    2
    3
    4
    5
    4
    2
    2
    4
    6
    8
    10
    5
    3
    3
    6
    9
    12
    15
    6
    4
    4
    8
    12
    16
    20
    7
    5
    5
    10
    15
    20
    25
    8
    6
    6
    12
    18
    24
    30
    9
    7
    7
    14
    21
    28
    35
    10
    8
    8
    16
    24
    32
    40
    11
    9
    9
    18
    27
    36
    45
    12
    10
    10
    20
    30
    40
    50
    13
    14
    		 First
    5
    15
    		 Second
    2
    16
    10

    B16=INDEX($C$3:$G$12,MATCH($B$15,$B$3:$B$12,0),MATCH($B$14,$C$2:$G$2,0))

    I am able, on this PC, to view pic files, many other members are not able. Next time, upload a small workbook sample, not a pic, please
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 06-23-2015, 02:11 PM #4
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: Automated value finder for a table help! 

    Please Login or Register  to view this content.
    Without a excelfile I should say try this.

    The same formula as FDibbins, only he was a little faster
    Last edited by popipipo; 06-23-2015 at 02:14 PM.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  5. 06-23-2015, 02:22 PM #5
    Joseph96
    Joseph96 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Re: Automated value finder for a table help!

    Sorry I will do that next time FDibbins, I have never been on this forum before and thought I did it correct.

    Your answers work perfectly both ways thankyou for your help and so speedy to, i thought i would be waiting for a while for a response.

    thank you Joseph
    Reply With QuoteRegister To Reply
  6. 06-23-2015, 02:28 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Automated value finder for a table help!

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)
    Reply With QuoteRegister To Reply
  7. 06-23-2015, 03:11 PM #7
    Joseph96
    Joseph96 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Re: Automated value finder for a table help!

    I have gave everyone that has helped on this thread reputation, and i will now mark it as resolved thank you again.
    Reply With QuoteRegister To Reply
  8. 06-23-2015, 04:11 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Automated value finder for a table help!

    Thanks, always appreciated
    Reply With QuoteRegister To Reply
  9. 06-24-2015, 02:44 PM #9
    Joseph96
    Joseph96 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Re: Automated value finder for a table help!

    Ok so i tried today to create my own table, the formula you guys suggested works well on the timetables but not that well on this one?

    it is not giving the correct result and i do not understand why???

    i have attached the actual excel document i hope that was correct i have also within the document highlighted my problem and what the answer should be, i hope it makes sense.
    Test.xlsx

    All help is appreciated, Thanks

    Joseph
    Reply With QuoteRegister To Reply
  10. 06-24-2015, 03:43 PM #10
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Automated value finder for a table help!

    Index / match / match in cell B17 (green cell).

    See the attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 06-24-2015, 03:53 PM #11
    Joseph96
    Joseph96 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Re: Automated value finder for a table help!

    Oeldere you are a legend, so putting $ signs in made it work that is incredible.

    I havwnt used excel for a while and i am very rusty, Now for example i have a number that is '56' it is greater than the 46 and should be rounded up to 61 how should i go about doing that. Should i use the IF / THEN?
    Reply With QuoteRegister To Reply
  12. 06-24-2015, 04:01 PM #12
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Automated value finder for a table help!

    No it was not the $.

    syntax:

    index / match (row) / match (column)

    And you have to include column D in your formula.

    new formula:

    =INDEX($D$2:$T$6,match($B10,$D$2:$D$6,0);match($B9,$D$2:$T$2,1)+1)
    Attached Files Attached Files
    Last edited by oeldere; 06-24-2015 at 04:06 PM.
    Reply With QuoteRegister To Reply
  13. 06-24-2015, 04:04 PM #13
    Joseph96
    Joseph96 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Re: Automated value finder for a table help!

    Ahhhh i understand now, Thankyou for teaching me.
    Reply With QuoteRegister To Reply
  14. 06-24-2015, 04:29 PM #14
    Joseph96
    Joseph96 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Re: Automated value finder for a table help!

    and how do i now make it so all number are put in the correct place so if it is a higher value than 76 but lower than 91 and so on for every piece of data on both the top and side?
    Reply With QuoteRegister To Reply
  15. 06-24-2015, 04:33 PM #15
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Automated value finder for a table help!

    see #12

    new formula:

    =INDEX($D$2:$T$6,match($B10,$D$2:$D$6,0);match($B9,$D$2:$T$2,1)+1)

    The first (red) 1 is for a not exact match (0 is the other option).

    The second (red) 1 is to find the above value (56 goes to 61 instead of 46).



    You can do the same with the row-statement.
    Reply With QuoteRegister To Reply
  16. 06-24-2015, 04:46 PM #16
    Joseph96
    Joseph96 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Re: Automated value finder for a table help!

    OK so the new formula you have created will round the values up to the larger data, for the entire spreadsheet once I input them into my excel worksheet.

    I am no longer on my PC so I can not test the excel document.

    I know what to do with the formula now so that has helped a lot.

    Thank you for your help, I will mark the thread as solved and if I still need help I will just reopen this one as if saves time re explaining on a new thread.
    Reply With QuoteRegister To Reply
  17. 06-24-2015, 04:56 PM #17
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Automated value finder for a table help!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

    It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
    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. Automated table
    By sije1204 in forum Excel General
    Replies: 0
    Last Post: 01-22-2014, 09:34 AM
  2. Automated Data Table
    By CJDB in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-14-2012, 05:11 AM
  3. automated copy in table
    By LiamF in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-07-2008, 05:37 AM
  4. Creating a automated table with an if function
    By mmoz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2008, 06:27 PM
  5. Totally Lost - Dates in huge table, Range finder
    By kempy1000 in forum Excel General
    Replies: 4
    Last Post: 08-15-2006, 03:04 PM

Tags for this Thread

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