Reverse Column and Row Look-up

NamthCub

New Member
Joined
Dec 12, 2010
Messages
17
Reverse Column and Row Look-up
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
We have been working all day on a very large cataloging file.
<o:p></o:p>
We want excel to point to the ROW and COLUMN location by searching a RANGE.
<o:p></o:p>
EXAMPLE:
<o:p></o:p>
<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" border=0 cellSpacing=0 cellPadding=0 width=448><TBODY><TR style="HEIGHT: 13.5pt"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 48pt; PADDING-RIGHT: 0.65pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom width=64 noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 48pt; PADDING-RIGHT: 0.65pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=64 noWrap>
A<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 48pt; PADDING-RIGHT: 0.65pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=64 noWrap>
B<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 48pt; PADDING-RIGHT: 0.65pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=64 noWrap>
C<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 48pt; PADDING-RIGHT: 0.65pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=64 noWrap>
D<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 48pt; PADDING-RIGHT: 0.65pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=64 noWrap>
E<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 48pt; PADDING-RIGHT: 0.65pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom width=64 noWrap>
F<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
1<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
5<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
45<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
3<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
12<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
2<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
35<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
5<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
46<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
7<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
3<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
47<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
4<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
3<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
12<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
4<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
30<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
32<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
44<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
0<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
5<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap x:num>
6<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
<o:p></o:p>


</TD></TR></TBODY></TABLE>​
<o:p></o:p>
<o:p></o:p>
We would like to type any number in the range B2:E5 into cell F1 and have it tell us the cell location example; if F1=12 we would get a result of D1 and D4, if F1=46 we would get an answer of D3.

More specifically we would like to receive Column D, Row 3 or referance the text in A3 and D1 as the result or the search

<o:p></o:p>
We are using Excel 2000 and Excel 2003
<o:p></o:p>
Thank you for your help and service.
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,775
If all the entries in A1:D4 are unique

=SUMPRODUCT(--(A1:D4=12)*ROW(A1:D4)) will return the row of the cell

=SUMPRODUCT(--(A1:D4=12)*COLUMN(A1:D4)) will return the column number
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,299
Here's my approach, in sections for readability:
F1: (a value to find in the table....eg 12)

This formula counts the number of matched items:
H1: =COUNTIF(A1:D4,F1)

This ARRAY FORMULA (commited with CTRL+SHIFT+ENTER)
begins the list of cell position references that contain the F1 value
I1: =SMALL(IF($A$1:$D$4=$F$1,COLUMN($A$1:$D$4)+(ROW($A$1:$D$4)-1)*COLUMNS($A$1:$D$4)),ROWS($1:1))

Note: that formula assumes the cells are in a grid,
with each cell sequentially numbered from left to right like this:
Code:
 1   2   3   4
 5   6   7   8
 9  10  11  12
13  14  15  16
This regular formula begins the list of row numbers that contain the F1 value
Code:
J1: =CEILING(I1/COLUMNS($A$1:$D$4),1)
and this regular formula begins the list of column numbers that contain the F1 value
Code:
K1: =MOD((I1-1),COLUMNS($A$1:$D$4))+1
Finally, this formula constructs the cell address for the matched items:
Code:
L1: =CELL("ADDRESS",INDEX($A$1:$D$3,J1,K1))
Copy those formulas down as far as you need.

Note: I purposely left errors un-managed to avoid cluttering up the formulas more than necessary.

Using your posted values, the formula return these values:
L1: $D$1
L2: $D$3

Is that something you can work with?
 

Forum statistics

Threads
1,081,545
Messages
5,359,433
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top