Position function of Mathematica in Excel for matr

问题:

I have a table in Excel with column and row headers and corresponding values. How do I lookup in Excel both the column and row header names/index by value in the table?

In Mathematica the equivalent function is Position[listoflist,value]

EDIT:

---

I made a simple function in VBA, but this is far from perfect

Function MathematicaPosition(lookvalue As Range, TableRange As Range, RowOrColumn As Boolean) As Integer
Dim r As Integer
Dim c As Integer
Dim tempindex As Integer
Dim i As Integer, j As Integer

tempindex = 0
r = TableRange.Rows.Count
c = TableRange.Columns.Count
For i = 1 To r
    For j = 1 To c 
        If lookvalue.Value = TableRange.Cells(i, j).Value Then
            tempindex = IIf(RowOrColumn, i, j)
        End If
    Next j
Next i
MathematicaPosition = tempindex
End Function

回答1:

See the double lookup section here:

http://www.cpearson.com/Excel/TablesAndLookups.aspx

回答2:

Lifted direct from MrExcel

Assuming data array is in A1:D5:

In G2: =COUNTIF(B2:D5,G1)

In G4: =IF(ROWS($G$4:G4)>$G$2,"",INDEX($A$2:$A$5,INT(SMALL(IF($B$2:$D$5=$G$1,(ROW($B$2:$D$5)-ROW($B$2)+1)*10^5+COLUMN($B$2:$D$5)-COLUMN($B$2)+1),ROWS($G$4:G4))/10^5)))

In H4: =IF(ROWS($G$4:G4)>$G$2,"",INDEX($B$1:$D$1,MOD(SMALL(IF($B$2:$D$5=$G$1,(ROW($B$2:$D$5)-ROW($B$2)+1)*10^5+COLUMN($B$2:$D$5)-COLUMN($B$2)+1),ROWS($G$4:G4)),10^5)))

The latter two entered with Ctrl+Shift+Enter and copied down.

I have no idea about Mathematica and don't understand reverse values (basically whether to multiply by -1 or switch columns over).

回答3:

Function MathematicaPosition(lookvalue As Range, TableRange As Range, RowOrColumn As Boolean) As Integer
Dim r As Integer
Dim c As Integer
Dim tempindex As Integer
Dim i As Integer, j As Integer

tempindex = 0
r = TableRange.Rows.Count
c = TableRange.Columns.Count
For i = 1 To r
    For j = 1 To c 
        If lookvalue.Value = TableRange.Cells(i, j).Value Then
            tempindex = IIf(RowOrColumn, i, j)
        End If
    Next j
Next i
MathematicaPosition = tempindex
End Function