Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Reverse table array lookup?

331 views
Skip to first unread message

Eric Bohn

unread,
Dec 6, 2011, 5:06:21 AM12/6/11
to
My situation is that I have a table like this:

A B C D E
1 Car Boat Plane 20
2 slowest 50 5 150
3 slower 55 8 160
4 slow 60 13 170
5 fast 70 17 190
6 faster 80 20 210
7 fastest 95 30 250

What I want to do is to match a numerical value to one of the values
in the table. I know that the value will equal one and only one value
that exists in the table. I want to return the row and column header
text values and combine them in a single cell.

So for example if the value im trying to match is in cell E1=20, then
I want to return "faster boat".

Is there a way of doing this, perhaps using the index and match
functions?

Ron Rosenfeld

unread,
Dec 6, 2011, 7:49:45 AM12/6/11
to
If Table is a defined name referring to your entire table range, e.g: $A$1:$D$7, and your lookup value is in E9, then:

This formula must be **array-entered**:

=INDEX(Table,MAX(IF(Table=E9,ROW(Table)))-ROW(Table)+1,1) &
" " & INDEX(Table,,MAX(IF(Table=E9,COLUMN(Table)))-COLUMN(Table)+1)

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

isabelle

unread,
Dec 6, 2011, 8:02:44 AM12/6/11
to
hi,

=INDIRECT(ADDRESS(MAX(IF(rng=E1,ROW(rng))),COLUMN(rng)))

=INDIRECT(ADDRESS(ROW(rng),MAX(IF(plg=E1,COLUMN(plg)))))

validate both of them with shift+ctrl+enter

--
isabelle

isabelle

unread,
Dec 6, 2011, 10:26:48 AM12/6/11
to
correction,

=INDIRECT(ADDRESS(MAX(IF(rng=E1,ROW(rng))),COLUMN(rng)))

=INDIRECT(ADDRESS(ROW(rng),MAX(IF(rng=E1,COLUMN(rng)))))

Eric Bohn

unread,
Dec 7, 2011, 12:11:25 AM12/7/11
to
On Dec 6, 7:49 am, Ron Rosenfeld <r...@nospam.net> wrote:
> correctly, Excel will place braces {...} around the formula.- Hide quoted text -
>
> - Show quoted text -

This solution worked, thanks Ron!

Eric Bohn

unread,
Dec 7, 2011, 12:12:06 AM12/7/11
to
This solution also worked, Isabelle, thank you.

Ron Rosenfeld

unread,
Dec 7, 2011, 7:29:19 AM12/7/11
to
On Tue, 6 Dec 2011 21:11:25 -0800 (PST), Eric Bohn <bsee...@yahoo.com> wrote:

>This solution worked, thanks Ron!

Glad to help. Thanks for the feedback.

doug...@gmail.com

unread,
May 15, 2013, 3:14:36 AM5/15/13
to
Hi,

I have a question...
Can it list out multiple results?

For example:
A B C D E
1 Car Boat Plane 20
2 slowest 50 5 150
3 slower 55 8 160
4 slow 60 20 170
5 fast 70 17 190
6 faster 80 20 210
7 fastest 95 20 250

Result: slow boat, faster boat, fastest boat

Thanks.

benmc...@gmail.com

unread,
Jun 4, 2013, 11:35:26 PM6/4/13
to
Doug,

I think this calls for a user defined function. Try this code out. To use it in a cell, use this format for the formula:

=ConcatTable(Table, Criteria)

Here is the UDF:

Function ConcatTable(rTable As Range, sValue As String)
Dim sResult As String
Dim rCell As Range

For Each rCell In rTable
If rCell.Value = sValue Then _
sResult = sResult & "; " & Cells(rCell.Row, rTable.Column) & " " & Cells(rTable.Row, rCell.Column)
Next

If Len(sResult) = 0 Then
ConcatTable = vbNullString
Else
ConcatTable = Right(sResult, Len(sResult) - 2)
End If

End Function
0 new messages