CODEWITHSUNDEEP
excelvbaexcel-formula
user14352137
user14352137

Reputation:

Return value of cell if specific cell contains value

Let say my SHEET1!A1 contains x then I want to get the value of SHEET1!B1, and my formula for that would be;

=IF(SHEET1!A1="x",SHEET1!B1,"")

To do that in the entire column I select the entire column and press ctrl + D.

The problem after that

  1. The file goes from 28KB to 40MB making the document lag and stop working even just when saving. This is because I used ctrl + D to copy down the formula.

  2. If SHEET1!A1 doesn't have x, it will return blank or empty cell. What I want is to skip it then fill only the second cell if it does contain x.

The expected output would be;
Expected Result

This is what I have;
Wrong Result

I'm thinking this is not possible using a formula, maybe a macro would do?

Upvotes: 0

Views: 195

Answers (1)

ceci
ceci

Reputation: 774

if you have office365 you can do this with the "filter" function:

  =FILTER(B:B;A:A = "x")

Upvotes: 1

Related Questions