I am running up against an Excel problem that has stumped me. I'm trying to reverse index a table. I have a table where I will be entering values from a list. Based on that list, I want to be able to look back and find out the specific column and row number it belongs to.
I've attached an example spreadsheet. I'd like to use a formula to identify the column and row of a table that the specific value lies in. Any help?
mlukich,
Try array-entering this formula in K2 and filling downIf you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
Then array-enter this formula in L2 and filling down.Formula:Please Login or Register to view this content.
Dave
@Mr. Dave Awesome formula.Originally Posted by FlameRetired
mlukich,
Try array-entering this formula in K2 and filling downIf you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.
Then array-enter this formula in L2 and filling down.Formula:Please Login or Register to view this content.
Need to make more dynamic .
Try
K2=IF($J2="","",MIN(IF($B$2:$F$6=$J2,IF(K$1="column",$B$1:$F$1,$A$2:$A$6)))) with control+Shift+Enter and drag towards the cell.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Ankur,
Yup. Nice one.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks