Reverse table array lookup?

[Eric Bohn]

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]

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?

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]

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]

correction,

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

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

validate both of them with shift+ctrl+enter

 

[Eric Bohn]

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.- Hide quotedtext -

- Show quoted text -

This solution worked, thanks Ron!

 

[Eric Bohn]

correction,

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

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

validate both of them with shift+ctrl+enter

This solution also worked, Isabelle, thank you.

 

[Ron Rosenfeld]

This solution worked, thanks Ron!

Glad to help. Thanks for the feedback.

 

[dougbsfei]

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.

 

[benmcclave]

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.

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