Reverse Column and Row Look-up

N

NamthCub

New Member
Joined
Dec 12, 2010
Messages
17
Reverse Column and Row Look-up
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><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:
Sort by date Sort by votes
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
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
888
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
3K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,226,867
Messages
6,193,426
Members
453,799
Latest member
shanley ducker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top