Log In
Register
Home
Active Member
November 8, 2021
Offline
Hoping someone can help me figure this out. I'm trying to select the column and row #'s based on the nearest lower #. For example, if I have 710, how would I write the formula to have it return column 4 and row 8? Any help would be greatly appreciated!
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | |
| 6 | 1 | 174 | 348 | 522 | 696 | 870 | 1,044 | 1,217 | 1,391 | 1,565 | 1,739 | 1,913 |
| 7 | 6 | 180 | 354 | 528 | 702 | 875 | 1,049 | 1,223 | 1,397 | 1,571 | 1,745 | 1,919 |
| 8 | 12 | 186 | 360 | 533 | 707 | 881 | 1,055 | 1,229 | 1,403 | 1,577 | 1,751 | 1,925 |
| 9 | 17 | 191 | 365 | 539 | 713 | 887 | 1,061 | 1,235 | 1,409 | 1,583 | 1,757 | 1,931 |
| 10 | 23 | 197 | 371 | 545 | 719 | 893 | 1,067 | 1,241 | 1,415 | 1,589 | 1,762 | 1,936 |
September 9, 2020
Offline
Hi Dave,
Here's a suggestion.
BR,
Lionel
Active Member
November 8, 2021
Offline
Thanks Lionel!! One follow-up though.....that lets me find the # to search for, but once I have that, how would I do the 2nd part of the problem where I have the formula return the column heading # (5) and row # (9)? I really appreciate your help with the first part!
September 9, 2020
Offline
Hi Dave,
This is the formatting solution for row and column headers.
BR,
Lionel
Active Member
November 8, 2021
Offline
Thanks again Lionel!!! Very cool way to show it in the table! I'm sorry I wasn't clearer and I'm sorry you're the only one responding :), but what I'm trying to do is have the formula populate a cell like you had the first one did when it showed the response of 713 after searching 716. So to have a cell that says "4" and another cell that says "9". Thanks for showing me the other cool result though!
July 16, 2010
Offline
Hi Dave,
See file attached. I used Lionel's clever formula and this formula to get the row number (there might be a more efficient way, but I didn't have time to fully understand the row number formula). From there the column number is easy.
Mynda
September 9, 2020
Offline
Hi Dave,
Sorry for the misunderstanding, I didn't catch the exact request.
Mynda gives you a very good solution. And I have another one.
BR,
Lionel
The following users say thank you to Lionel Baijot for this useful post:
Mynda Treacy
1 Guest(s)
