7

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:

  • the lookup to be done in all the columns of this two-dimensional array and

  • have the function in one cell only

Improve this question
2
  • Isn't this a two-dimensional array? The row dimension and the column dimension? If so, consider changing the question to make it easier for other people to find in case a satisfactory solution is provided. Commented Nov 12, 2014 at 16:57
  • 1
    updated (is that what you mean?) Commented Nov 12, 2014 at 17:01

4 Answers 4

Reset to default
14

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Your assumption is correct that's exactly what I wanted. Thanks a lot!
Great! Glad to hear it!
Doesn't work for me. Created similar example, and it returns zero. Excel for Mac 2011. Different implementation?
No experience of that version - sorry.
1

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())
Improve this answer

1 Comment

ok this works but I need to have the function in one cell only
0

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.

Improve this answer

2 Comments

this is not what I want E1:E7 is just the first column of the array
Can you be more specific or provide additional clarity in your examples? You could copy that formula across the columns and concatenate the results?
-1

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
Improve this answer

1 Comment

Not only is this VBA code which the OP explicitly does not want, it also doesn't do what the OP wants.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.