CODEWITHSUNDEEP
arraysexcelmultidimensional-arrayexcel-formula
zlinks
zlinks

Reputation: 1067

excel match value(get row) in two-dimensional array

I have the following structure in my excel sheet.

          E           F           G        H        
      ---------   ---------    ------    -------      
 2 |  blah        sometext     atext      smth
 3 |  textval     accc
 4 |  test        avfr
 5 |  check       
 6 |  ertyui      
 7 |

Can you suggest a way using EXCEL functions (not VBA) of getting back the row of a matching string?

For example: given test I need to get back 4, given ertyui 6

I need:

Upvotes: 6

Views: 19931

Answers (4)

Mhadhbi issam
Mhadhbi issam

Reputation: 420

use this vb function it takes the table as argument and the of col variable and row variable ( the table inserted in the function contain the row of row values and col names , in other word the all table ) :

Public Function find_by_x_y(table_range As Range, row_val As Variant,col_val As Variant, error_finding_value As Variant) As Variant

Dim row_index_found As Integer
Dim col_index_found As Integer
row_index_found = 0
col_index_found = 0
Dim i As Integer
Dim j As Integer

For i = 2 To table_range.Rows.Count
    If table_range.Cells(i, 1).Value = row_val Then
        row_index_found = i
    End If
Next i

For j = 2 To table_range.Columns.Count
    If table_range.Cells(1, j).Value = col_val Then
        col_index_found = j
    End If
Next j
'MsgBox (" i = " & row_index_found & " , j = " & col_index_found)
If col_index_found <> 0 And row_index_found <> 0 Then
    find_by_x_y = table_range.Cells(row_index_found, col_index_found).Value
    Exit Function
Else
    find_by_x_y = error_finding_value
    Exit Function
End If

End Function

Upvotes: -1

XOR LX
XOR LX

Reputation: 7742

With your search string in A1:

=SUMPRODUCT((E2:H6=A1)*ROW(E2:H6))

Obviously this will fail if there is more than one occurrence of the search string within the range. However, each of the values in the dataset you provide is unique, so I presume that this is not an issue.

Besides, if that was a possibility, you would need to clarify which of potentially several different row numbers should be the preferred return.

Regards

Upvotes: 13

JasonAizkalns
JasonAizkalns

Reputation: 20463

Look up examples using =MATCH()

=match("test", E1:E7, 0)

This should return 4. Then modify accordingly.

Better yet, place your condition in some other cell, say A1 and then you could use:

=match($A$1, E$2:E$7, 0)-1

Place that formula in cell E1 and drag across the columns and you'll have a match per each column.

Upvotes: 0

pnuts
pnuts

Reputation: 59460

Maybe with the string sought in say J1, in K1 (the output) something like: 

 =SUM(K2:K100)

and in K2 copied down to say K100: 

=IF(ISERROR(MATCH(J$1,E2:I2,0)),"",ROW())

Upvotes: 1

Related Questions