Search the entire sheet and return an array of cell reference

1
votes

Hi I am new to Excel/Google Spreadsheet.

I have a problem that I want to search the entire sheet for a given string.

For example the table looks like

    A    B    C    D    
1   foo  1    bar  2
2   bar  9    abc  3
3   foo  2    bar  4

LOOKUP/MATCH/VLOOKUP can only search one row or column, I need a formula to search for the whole sheet for 'bar', and return the array of all found cells, e.g. {$C$1, $A$2, $C$3}.

What's more (the ultimate goal) is to calculated the sum of the numbers next to the found cells, in this example, 2+9+4=15.

I hope this can be achieved without VBA so that I can use the formula in Google Spreadsheet as well.

1
google-sheetsexcel-formula

1 Answers

1
votes

For your example, in Excel:

=SUM(IF(A1:C3="foo",B1:D3,0)) entered as an array formula, with ctrl-shift-enter

In Google:

=ARRAYFORMULA(SUM(IF(A1:C3="foo",B1:D3,0)))

The ranges can be as large as you like. The important points are that the first range covers all of the values you want to look for text in, and that the second range is the same size but shifted one cell to the right.