Find Row OR Column Header for a cell in a two way table WITHOUT VBA

**militiamc** · 03-25-2015, 09:32 AM

Here's some example data:

.. A B C
E 1 4 6
F 2 5 9
G 7 3 2

So let's say I know the number 9 is in the table and I know it's a 3X3 table but I don't know what row OR what column its in, how do I find the row or column (I know that given one you can find the other)? V & H lookups won't work. I don't think Match will work on an array. I ended up splitting data a column at a time to match only the row, but how can I do it all in one formula in one cell?

Bonus quesetion: The number 2 shows up twice. How could I output row and column for both of them?

ALL WITHOUT VBA PROGRAMMING!

Thanks so much for the help!

**XOR LX** · 03-25-2015, 09:48 AM

Hi.

You don't state whether it's possible for a given value to appear more than once in a given row or column, so I've assumed that it isn't.

With your search value in D1, to return the relevant rows:

=IF(ROWS($1:1)>COUNTIF(Range1,$D$1),"",AGGREGATE(15,6,ROW(Range1)/(Range1=$D$1),ROWS($1:1)))

Replace Range1 with your actual range and copy down until you start to get blanks.

Identical formula for columns but replacing:

ROW(Range1)

with:

COLUMN(Range1)

Regards

**tim201110** · 03-25-2015, 09:53 AM

or this way

**militiamc** · 03-25-2015, 10:59 AM

XOR LX that works perfectly!! Thanks!
I know that given duplicate values it would just return the first occurrence observed. And then you might have to make another column to handle duplicates where it says if you experience a duplicate you give its new position.
Would you know how to do that? You already know the address of the first occurance. Can your formula be edited to search a range but ignore a certain cell within the range?

Thanks!

**XOR LX** · 03-25-2015, 11:01 AM

Sounds doable, but could I ask you to present an actual example together with your expected results?

Regards

**militiamc** · 03-25-2015, 12:09 PM

Hi XOR,

Attached in an Excel. I am essentially sorting all data based on price and then outputing what is available at each price level. The problem is if I have a duplicate price then the second, third and nth occurance of this duplicate will give the wrong answer.

Would you know how to get around this?

Excel attached.

Thanks!

**militiamc** · 03-25-2015, 12:23 PM

tim201110 thanks for your Excel. It worked on your provided sample, but when I tried to apply it to my model (move it) I kept getting errors (I tried tweaking the formula and used the CTR+SHIFT+ENTER for arrays as well but nothing worked).

**XOR LX** · 03-25-2015, 12:35 PM

Ok, first of all I'm not sure why you've changed the ROWS($1:1) part in my solution to COLUMNS($A:A), since these formulas are intended to be dragged down, not across.

Secondly, constructions using INDIRECT and ADDRESS are best avoided if possible (which it almost always is) due to their inherent volatility.

Thirdly, you can employ a COUNTIF construction for AGGREGATE's k parameter to get round the duplicates issue.

In E21:

=IF(ROWS($1:$1)>COUNTIF($E$12:$G$17,D21),"",INDEX($E$11:$G$11,AGGREGATE(15,6,COLUMN($E$11:$G$11)-MIN(COLUMN($E$11:$G$11))+1/($E$12:$G$17=D21),COUNTIFS(D$21:D21,D21))))

In F21:

=IF(ROWS($1:$1)>COUNTIF($E$12:$G$17,D21),"",INDEX($D$12:$D$17,AGGREGATE(15,6,ROW($D$12:$D$17)-MIN(ROW($D$12:$D$17))+1/($E$12:$G$17=D21),COUNTIFS(D$21:D21,D21))))

Cheers

**bebo021999** · 03-25-2015, 12:50 PM

How about in E21:

=INDEX($11:$11,LARGE(IF($E$12:$G$17=$D21,COLUMN($E$12:$G$17),""),COUNTIF($D$21:$D21,$D21)))

F21:

=INDEX($D:$D,LARGE(IF($E$12:$G$17=$D21,ROW($E$12:$G$17),""),COUNTIF($D$21:$D21,$D21)))

Both are array formula must be confirmed by Ctrl-Shift-Enter

**XOR LX** · 03-25-2015, 01:01 PM

@bebo

Isn't that just identical to mine apart from a few minor details?

Regards

**bebo021999** · 03-25-2015, 01:12 PM

@XOX LX,
In fact, I have never used AGGREGATE before, I tried but still not to become its friend...

I refer to LARGE in this kind of work

**bebo021999** · 03-25-2015, 01:16 PM

There is bug in XOX LX and mine, too:
If there is value triplicates two rows+two columns in table,i.e: 214 in E14,E15,F14
throw up all !

**XOR LX** · 03-25-2015, 01:45 PM

Originally Posted by bebo021999

@XOX LX,
In fact, I have never used AGGREGATE before, I tried but still not to become its friend...

I refer to LARGE in this kind of work

Actually I prefer your idea of INDEXing the entire row/column, which means that the constructions I used for generating the row/column values are not necessary.

Originally Posted by bebo021999

There is bug in XOX LX and mine, too:
If there is value triplicates two rows+two columns in table,i.e: 214 in E14,E15,F14
throw up all !

Can you clarify? I can't see how this is an issue?

Regards

**militiamc** · 03-25-2015, 05:03 PM

@XOR LX & @bebo
Bebo as you mentioned both your formulas can handle multiple duplicates if across the same row or column but 3 or more duplicates spread about creates incorrect answers. Can this be avoided?

@XOR LX
If I don't use the INDIRECT(ADDRESS) how else would you suggest I get the column/row header?

**XOR LX** · 03-26-2015, 03:14 AM

Originally Posted by militiamc

@XOR LX & @bebo
Bebo as you mentioned both your formulas can handle multiple duplicates if across the same row or column but 3 or more duplicates spread about creates incorrect answers. Can this be avoided?

Can someone please clarify this issue with an example?

Originally Posted by militiamc

@XOR LX
If I don't use the INDIRECT(ADDRESS) how else would you suggest I get the column/row header?

With INDEX, as both bebo and I used.

Regards

**ELeGault** · 03-26-2015, 03:55 AM

Can I ask the need to see the cross reference -

Seems like dropping this into a Pivot would be much simpler, can identify a count of values based on selection in a slicer which in turn can tell you the week, Type, Rate or even sorted (which is an ID) value.

Could also apply conditional formatting that makes the desired amount pop in an obnoxious color...

Just curious if this is necessary for what the actual end result is... Perhaps knowing how this will be of use other suggestions/recommendations on how to get to the goal can be brought about...

Cheers

**militiamc** · 03-29-2015, 11:42 AM

@XOR LX

If there at are least 2 duplicates in the same row AND at least 2 duplicates in the same column then the formulas don't work. Please see attached worksheets

**XOR LX** · 03-30-2015, 05:00 AM

Ah, yes. Of course. Thanks.

Perhaps change the formula in F21 to:

=IF(E21="","",INDEX($D$12:$D$17,AGGREGATE(15,6,ROW($D$12:$D$17)-MIN(ROW($D$12:$D$17))+1/(INDEX($E$12:$G$17,,MATCH(E21,$E$11:$G$11,0))=D21),COUNTIFS(D$21:D21,D21,E$21:E21,E21))))

though I imagine there are simpler solutions!

Regards

**militiamc** · 03-30-2015, 07:27 PM

@XOR LX

Thanks that works perfectly!

**XOR LX** · 03-31-2015, 02:23 AM

You're welcome!

Find Row OR Column Header for a cell in a two way table WITHOUT VBA

LinkBack

Thread Tools

Rate This Thread

Display

Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Re: Find Row OR Column Header for a cell in a two way table WITHOUT VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find name in a table and return the column header

[SOLVED] Find Row in Table based on Column Header

Find first visable cell under header of column A.

[SOLVED] Find specific value in table and return header row and header column value

[SOLVED] Find last non blank cell in a row then return column header

Bookmarks

Bookmarks

Posting Permissions