• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reverse lookup

I

iagainsti

New Member
Hey guys,


I'm new here and have a good one. I'm trying to find a given value in an array, then return the column and row headers.


So, let's say I have a sheet with dates as colum headers, and states as row headers. I then want to look at the largest ten numbers from the array. So I do a LARGE, etc. to get those 10 numbers, but now I want to know where they are?


Ideas?
 
Could you perhaps use conditional formatting to highlight the cells that are as large or larger than your 10th largest value?
 
Hi, iagainsti!

Nice problem. And the problem is that inverse looking up functionality is only implemented in Excel for uni-dimensional arrays (vector, not matrix).

Give a look at this file, it's an example of the top 5 values in a 6 by 3 table. Expand as you need.

http://dl.dropbox.com/u/60558749/Reverse%20lookup%20%28for%20iagainsti%20at%20chandoo.org%29.xlsx

Besides CF idea taken from Phuzzy's post it shows you row & col for top X (light blued A1 cell) values. And if it works for you, well, there's only one issue remaining: dealing with duplicates. I leave it to you, just play around MAX functions on rows and columns... good luck!

Regards!
 
Thanks for the info, guys...while searching around, I found a solution on another forum. To be honest, I could not have come up with it, but I'd like to share it as I think others would find it useful.


I can not upload a file as I'm at work, but I will paste in the formula


This is an array function, so, of course, you need to CTR+SHIFT+ENTER for it...but you allready knew that...


=IF(ROWS($A$1:$A$1)>COUNTIF($B$2:$E$5,$F2),"",INDEX($A$2:$A$5,SMALL(IF($B$2:$E$5=$F2,ROW($B$2:$E$5)-ROW($B$2)+1,""),ROWS($A$1:$A$1))))


In this case, the data is in cell a1:e5, the value we are searching the array for is in cell F2


To get the column heading you would use


=IF(G2="","",INDEX($A$1:$E$1,,MATCH(F2,INDEX($A$2:$E$5,MATCH(G2,$A$2:$A$5,0),0),0)))


entered normally (not an array function).


It would be an interesting formula forensic topic...
 
Hi, iagainsti!


Wow, what amazing formula that you find...

I can't get it working, see what it shows to me:


-----

[pre]
Code: 
46	7	38	64	32	max	row	column
32	22	92	10	67	93	58	64
58	11	39	93	28
64	39	70	18	46
45	81	88	77	51
[/pre]
-----


In my sample data at A1:E5 the highest value is 93 and is located in D3, so I expected to be returned a 3 for row and a 4 for column... but I get 58 and 64 instead. Also tried to move the whole set and formulas to another location in sheet, adjusted cell references, and got the same result.


Any ideas? Surely I'm doing something wrong which I cannot identify. Would you please try to apply the formulas to this data set? Thanks.


Regards!
 
I think it's working fine...add row and colum headers and you'll see what I mean...


As it is, it thinks that the row is "58" and the column is "64" so that is correct...


Just try addin text headers and I think it will work
 
Hi, iagainsti!


Thanks for the tip, it's working now. It retrieves the row and column labels (header and left). For getting the row and column numbers you can use two match functions:

#row: =MATCH(G2,A2:A5,0)

#col: =MATCH(H2,B1:E1,0)


Regards!
 
Hi,


I had to do a reverse look-up earlier this week for work. See the linked file for what I threw together. Didn't fully test or make the most efficient but believe it does the trick, including treatment of duplicates.


Thanks

Kyle


https://skydrive.live.com/redir.aspx?cid=9a0721b634391421&resid=9A0721B634391421!143&parid=9A0721B634391421!136


file: Reverse Lookup
 
You must log in or register to reply here.
Back
Top