I have this formula:
=IF(Sheet1!C2="x", Sheet1!B2,"")Its function is to match if the cell from Sheet1 in column C is the same as the character x; if true then get the value of Sheet1 in column B and if false leave it blank.
Here's the sample data of Sheet1:
And here's the result using the formula:
But what I wanted is:
The blank cell happens because
=IF(Sheet1!C2="x", Sheet1!B2,"what to replace here to not to add a blank cell")2 Answers
I think you want to retrieve all the samples from Sheet1 associated with an "x" without any intermediate blanks. So if Sheet1 is like:
In Sheet2, cell A1 enter the array formula:
=IFERROR(INDEX(Sheet1!$B$1:$B$15,SMALL(IF(Sheet1!$C$1:$C$15="x",ROW($B$1:$B$15)),ROW(1:1))),"")and copy downward.
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.
If you are willing to add a "helper" column to either Sheet1 or Sheet2, the ugly array formula can be greatly simplified.
1If you don't want blank cells, when the condition returns False, in this case you can replace blank cells with any meaningful text.
You may rewrite your formula like this:
=IF(Sheet1!C2="x", Sheet1!B2,"Doesn't Match")
