Thanks for the formula! I went through what you did and applied it to the
table/spreadsheets I'm working on. It works great! I really appreciate the
help. It even made something else I was doing much easier to replicate the a
bunch of similar spreadsheets!

"T. Valko" wrote:

just choosing one, either the first row with the number
or first column with the number would be fine.

Ok, this will find the top-most, left-most instance.

Based on that little sample table I posted...

tbl refers to B2:D4

A10 = lookup value = 6

Array entered** :

=INDEX(B1:D1,MATCH(A10,INDEX(tbl,MIN(IF(tbl=A10,RO W(tbl)-MIN(ROW(tbl))+1)),0),0))&"
"&INDEX(A2:A4,MIN(IF(tbl=A10,ROW(tbl)-MIN(ROW(tbl))+1)))

Note that there is a space between &" "&.

Line wrap will usually break at that point so it will appear that the space
character is not there.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"car guy" wrote in message
...
In the application I'm working on, I've come up with a little matrix and
logic to do the reverse lookup I am trying to find an easier solution for
here and I have run into this exact situation (i.e. multiple locations for
the number looked up.) With my matrix, I have not been able to come up
with a
good solution, however, in the real leaf exaple I'm working on, just
choosing
one, either the first row with the number or first column with the number
would be fine. Unfortuntely, my Matrix have have so far, can't match the
two
up and add the column/row titles from the same number. This is my dilema.

"T. Valko" wrote:

What if there were more than 1 instance of 6:

......A.....B.....C.....D
1..........xx.....yy....zz
2...aa.....8......4......6
3...bb....6......3......7
4...cc....5......2......0


--
Biff
Microsoft Excel MVP


"car guy" wrote in message
...

Wow, that didn't format like I wanted...

Imagine a 3x3 table with headings of parts across the top (columns) and
causes down the side (rows), vaules I tossed up left to right then down
were
12,16,4,4,6,3,2,1,0.


"car guy" wrote:

As a very simplified table:

A B C
D
1 Part Table/Issue Hammer Screwdriver Wrench
2 Broken 12 16
4
3 Missing 4 6
3
4 Dirty 2 1
0


For what I'm doing I know "6" is the value I have and need to go back
to
the
table and get the Column name and Row name (and combine them to one
cell
using &""&) to show the results of the number look up. In this case, a
resulting cell with "Screwdriver Missing" after coming up with "6" in
a
cell.

"RagDyeR" wrote:

Are you looking for the cell address of the value,
or do you need the data in the column and row headers of their
intersection?

Give some idea of your datalist configuration.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit
!
-----------------------------------------------------------------------------------------------

"car guy" <car wrote in message
...
I have reviewed information on how to retrieve a piece of data from
a
Lookup
table based on looking up a value in the columns and a value in the
rows. I
am working on an application where I need to do the reverse of this.
(i.e. I
have a value and I need to know the column and the row the value is
found
in.) VLOOKUP and HLOOKUP only work for one-dimensional arrays and
only
if
the
data is in order. I would think that this would be a common need.
Has
anyone
figure an easy way to do this in an Excel spreadsheet. (I'm using
Excel
2002
at work.)