I'm trying to figure out a formula that returns the value in Column B if Column A matches a particular string.
For example, if A1 matches the string "Example" then return the value in B1, however, I want this to be done across multiple rows of data at the same time.
| A | B | C |
|---|---|---|
| My example | 100 | =IF(ISERROR(FIND("example",A1)),,B1) |
The FIND function is case-sensitive. If you need a case-insensitive search use the SEARCH function.
You can use a reference to a cell containing your search string as an alternative to hardcoding it in the formula. In the following example the search string is in $D$1. The reference is absolute so that when you copy the formula down the reference to $D$1 doesn't change.
| A | B | C | D |
|---|---|---|---|
| My example | 100 | =IF(ISERROR(FIND($D$1,A1)),,B1) | Example |
=INDEX(IF(A1:A1000="Example", B1:B1000,) Uses a simple IF function where the value_if_true is Column B and the value_if_false is an empty value.
The IF function is wrapped in INDEX which causes it to behave as an array function thus applying the formula across the range, Rows 1 to 1,000 in this example. ARRAYFORMULA could be used instead but INDEX is less characters to type.
=MAP(A1:A1000, B1:B1000, LAMBDA(a, b, IF(a="Example", b,))) More complicated approach MAPs the column values row-by-row into a LAMBDA function that stores the current row's values in variables a and b respectively and the LAMBDA's formula then performs the test using the variable names until all rows are completed.
In C1 enter:
=filter(A1:B6,A1:A6="example") 
make the "6" whatever you need.
