# [SOLVED] Find Adjacent Row and Column - Screen included

1. ## [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

2. ## 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.

3. ## Re: Find Adjacent Row and Column - Screen included

Will ther ALWAYS be only one possible answer?

4. ## 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. ## Re: Find Adjacent Row and Column - Screen included

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

6. ## Re: Find Adjacent Row and Column - Screen included

Originally Posted by Glenn Kennedy
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. ## 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...

8. ## 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. ## Re: Find Adjacent Row and Column - Screen included

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.

10. ## 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. ## Re: Find Adjacent Row and Column - Screen included

Solution provided at post #7.

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

and

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

12. ## Re: Find Adjacent Row and Column - Screen included

You're welcome.

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. ## 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.

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

#### 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