+ Reply to Thread
Results 1 to 13 of 13

[SOLVED] Find Adjacent Row and Column - Screen included

  1. #1
    Registered User
    Join Date
    10-21-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    9

    [SOLVED] Find Adjacent Row and Column - Screen included

    Hi All,

    Im trying to figure out the best way to get around this problem.

    I have a table consisting of values, i want to be able to look up any value in that table and return the number ive alocated to its position in the column and row. The value will change in its location .

    The circled red cells would the results i am looking for based on the search for 500

    Any help would be greatly appreciated

    Capture.JPG
    Last edited by thegr812k; 07-06-2019 at 09:34 AM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Find Adjacent Row and Column - Screen included

    What are the dimensions of your table, it looks like it is 18 columns by at least 30 rows, the actual dimensions would be helpful for people to create a formula for your results.
    AND, do any of the numbers you are searching for repeat? And if I'm understand correctly you want two formulas, one to return the horizontal number and one to return the vertical number in a different cell.
    Probably uploading a sample workbook would go a long way to help get the correct formulas.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Find Adjacent Row and Column - Screen included

    Will ther ALWAYS be only one possible answer?
    Glenn



  4. #4
    Registered User
    Join Date
    10-21-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Find Adjacent Row and Column - Screen included

    Thanks for your reply, the table dimension with vary once i work out how to do it You are correct i want 2 formulas. The number will not repeat, it will only ever appear once. My actual sheets is much more complex with different thing going on, so ill just export that section and upload now as once i work it out ill incorporate into the main workbook thank you

  5. #5
    Registered User
    Join Date
    10-21-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Find Adjacent Row and Column - Screen included

    Quote Originally Posted by Glenn Kennedy View Post
    Will ther ALWAYS be only one possible answer?
    Yes the value will only show once, but will be in different locations

  6. #6
    Registered User
    Join Date
    10-21-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Find Adjacent Row and Column - Screen included

    Quote Originally Posted by Glenn Kennedy View Post
    Will ther ALWAYS be only one possible answer?
    Sorry struggling to see how to upload file, apparently i havent enough posts to share links to Google drive etc

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Find Adjacent Row and Column - Screen included

    Row:
    =SUMPRODUCT(--($E$2:$E$14)*($F$2:$K$14=$A3))

    Column:
    =SUMPRODUCT(--($F$1:$K$1)*($F$2:$K$14=$A3))

    if this is not what you want, post a sample sheet, not a picture of one...
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Find Adjacent Row and Column - Screen included

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    BUT try out the file from the previous post FIRST.

  9. #9
    Registered User
    Join Date
    10-21-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Find Adjacent Row and Column - Screen included

    Please see attached.

    So to clarify i have simplified a example chart. I will like to be able to search for any value (in this case ive used 500) , locate if position in the chart and return the vertical number and horiozantal number based on its location.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-21-2013
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Find Adjacent Row and Column - Screen included

    Great thank you! That should work a treat, and i can see that if is repeats it would cause issues but luckily i wont. Great idea, would never have thought of using SUMPRODUCT . Thanks !

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Find Adjacent Row and Column - Screen included

    Solution provided at post #7.

    Adjust formulae to suit your ranges.

    =SUMPRODUCT(--($E$5:$AC$5)*($E$6:$AC$45=$B$7))

    and

    =SUMPRODUCT(--($D$6:$D$45)*($E$6:$AC$45=$B$7))

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Find Adjacent Row and Column - Screen included

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and 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 all members who helped you reach a solution.

  13. #13
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Find Adjacent Row and Column - Screen included

    Wow, a lot took place in the short time I was away. Glad Glenn solved it for you, knew once he saw it you were in good hands if I couldn't stay online. Don't forget to mark as solved.
    Last edited by Sam Capricci; 07-06-2019 at 10:11 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need to make a macro to find values in a table column, pictures included
    By smithian91 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-21-2017, 11:45 AM
  2. Replies: 17
    Last Post: 02-02-2017, 04:13 PM
  3. Replies: 2
    Last Post: 11-16-2016, 11:40 AM
  4. Looping 'find' to find a value and replace a value in an adjacent column
    By Boltsie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2016, 06:54 PM
  5. [SOLVED] Find first non-zero value in a column and return adjacent value in column to the left?
    By hchavous in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-24-2015, 06:11 AM
  6. [SOLVED] Macro to find the empty cell in a column and copy a adjacent row to another column.
    By naga in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-18-2013, 07:44 AM
  7. macro to find first blank cell in a column cut the value from left adjacent column
    By willykin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-01-2012, 09:23 AM

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