CODEWITHSUNDEEP
excelexcel-formula
thegigabyte
thegigabyte

Reputation: 131

Get the value of the adjacent cell if a cell contains a specific string

I'm looking for a formula that would get me the value of the adjacent cell if any cell in the row matches the word "fizz" exactly.

This is what my spreadsheet looks like:

1 | 
2 | 
3 | fizz
4 | 
5 | buzz
6 | fizz
7 |

and I'm looking for a formula that would return an array or a list similar to this if I searched for fizz

3
6

Upvotes: 0

Views: 1919

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27273

You may try:

FORMULA_SOLUTION

• Formula in F2:

=FILTER(A2:A8,$D$2=B2:B8,"None")

Or, for those who already have TAKE() may try:

=FILTER(TAKE(A2:B8,,1),D2=B2:B8)

Or, for those who are using earlier versions may try:

=IFERROR(INDEX($A$2:$A$8,
AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/
($D$2=$B$2:$B$8),ROW(A1))),"")

The above may need to press CTRL + SHIFT + ENTER depending on your Excel Versions!

Upvotes: 2

Related Questions