0

I have this formula =IF(Sheet1!C2="x", Sheet1!B2,"") which function is to match if the cell from Sheet1 in column C is same as the character of 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 enter image description here

and here's the result using the formula enter image description here

but what I wanted is enter image description here

The blank cell happens because =IF(Sheet1!C2="x", Sheet1!B2,"what to replace here to not to add a blank cell").

1

I think you want to retrieve all the samples from Sheet1 associated with an "x" without any intermediate blanks. So if Sheet1 is like:

enter image description here

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.

enter image description here


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.

  • Exactly what I wanted, even though the formula is complicated. – KrixMux Deadpool Apr 30 '18 at 3:28
-1

If 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")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.