Is there a way to do an inverse two-way lookup?

[car guy]

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.)

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------

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.)

 

[car guy]

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.

 

[car guy]

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.

 

[T. Valko]

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

 

[car guy]

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]

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 B24

A10 = lookup value = 6

Array entered** :

=INDEX(B11,MATCH(A10,INDEX(tbl,MIN(IF(tbl=A10,ROW(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)

 

[car guy]

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]

You're welcome. Thanks for the feedback!

 

[Narnimar]

Hi T. Valko,
Your solution here close to my question which I could not get the answer
from anywhere, I would like to ask you.
I got a database table in a file from which a formula need to return the
cell addres if a lookup value found. In my table the look up value will occur
once and is a text for your information. Any further information required
please revert back.

 

[T. Valko]

Assume the range of interest is A1:E5.

A10 = your lookup value

Array entered** :

=ADDRESS(MAX((A1:E5=A10)*ROW(A1:E5)),MAX((A1:E5=A10)*COLUMN(A1:E5)),4)

If you want an error trap for when the lookup value is not present:

=IF(COUNTIF(A1:E5,A10),ADDRESS(MAX((A1:E5=A10)*ROW(A1:E5)),MAX((A1:E5=A10)*COLUMN(A1:E5)),4),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.