Vlookup to only display cells with matching values

[PurpleButterfly]

Hello lovely humans,

I'm trying to create a summarised list of all comments next to cells with the value of "N". So far I have =VLOOKUP("N",N4:O21,2,FALSE) and I've dragged it down and it's automatically made the next one be N5:O22 etc. The rest of the values remain the same. I'm getting it do the Vlookup in column P from row 4 onwards. (Not sure if this is right)

I tried =IF(VLOOKUP("N",N4:O21,2,FALSE)="",VLOOKUP("N",N5:O21,2,FALSE),VLOOKUP("N",N4:O21,2,FALSE)), but I got the same result, as described below:

If the first occurrence of N is in N5 and the second is in N11, it's returning the corresponding value of N5 in both P4 and P5, then it's returning the corresponding value of N11 in P6-P11, and #N/A for the rest of the rows.

If the "N" is in N5 and N11 I'm trying to get it to return O5 in P4 then O11 in P5.

I hope this makes sense, if anyone can help me that would be really awesome!

 

[Peter_SSs]

Welcome to the MrExcel board!

If the "N" is in N5 and N11 I'm trying to get it to return O5 in P4 then O11 in P5.

Is this what you mean?

22 03 31.xlsm
	N	O	P
1
2
3
4		a	b
5	N	b	h
6	N/A	c	k
7		d
8		e
9		f
10		g
11	N	h
12		i
13		j
14	N	k
15
N

Cell Formulas
Range	Formula
P4:P6	P4	=FILTER(O4:O20,N4:N20="N","")
Dynamic array formulas.

 

[PurpleButterfly]

Yes! Exactly that!

 

[PurpleButterfly]

Thank you so much!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.