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 |
Hi Dave,
Here's a suggestion.
BR,
Lionel
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!
Hi Dave,
This is the formatting solution for row and column headers.
BR,
Lionel
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!
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
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
