jeudi 13 juin 2019

How to create a list by searching if a column contains a value?

I have in column A values (red, white) and in column B values (Marie, Jane, David, Jack etc.) There are several hundreds of rows so that there are different names once but for each name a color (red or white) is assigned. So for example:

column A column B

red Marie

red Jane

white David

red Jack

white Ashley

etc.

I want to search all names with color white and make a list of names to column C.

I know IF-statement is simplest solution BUT I don't want blank cells inbetween. I want a full list of names so that there are no useless cells. So =IF(A1="white"; B1; "") would not work because I don't want the "" part. Instead, is it possible to move to next cell to see if that cell/row includes the word white? And if so, it would return the value next to the cell "white".

I have also tried INDEX-MATCH but it only returns the first value to when I try to use autofill. So the name Marie would just copy hundred times.

VLookup hasn't helped me either.

Aucun commentaire:

Enregistrer un commentaire