lookup help

[bflan0524]

hello i have a data sheet below

Viewers	Last View	Views
Flanagan, Brian	1 week	53
Doe, John	4 weeks	100

i want to create a new sheet like below, where i can reference the first sheet and bring in the data to look as below, can somebody help with that on a formula

Column A	Column B	Column C	Column D	Column E
Name	1 Week	2 Weeks	3 Weeks	4+
Doe John	N	N	N	Y
Flanagan, Brian	Y	N	N	N

 

[Kaper]

Assuming your upper table is in columns K:M and the lower one starts at A1
In cell B2 (showing N for 1 week for Doe, John - names have to match exactly):

=IF(--(SUBSTITUTE(SUBSTITUTE(VLOOKUP($A2,$K$1:$L$13,2,0)," week",""),"s",""))=COLUMNS($B$1:B$1),"Y","N")

copy this right and in E2 make a minor change to allow for 4+ not only 4:

=IF(--(SUBSTITUTE(SUBSTITUTE(VLOOKUP($A2,$K$1:$L$13,2,0)," week",""),"s",""))>=4,"Y","N")

 

[MountainFog]

You could also load it into powerquery or pivot table and just pivot column on Last View

 

[Kaper]

If it's true that [COLOR=rgb(0, 0, 0)]bflan0524[/COLOR] still uses Excel 2010 (as shown in a profile), PQ has to be first downloaded (it is available at Download Power Query from Official Microsoft Download Center ) and installed.

 

[bflan0524]

am i using the top or the bottom one, i tried the bottom one and it wasnt working

Assuming your upper table is in columns K:M and the lower one starts at A1
In cell B2 (showing N for 1 week for Doe, John - names have to match exactly):

=IF(--(SUBSTITUTE(SUBSTITUTE(VLOOKUP($A2,$K$1:$L$13,2,0)," week",""),"s",""))=COLUMNS($B$1:B$1),"Y","N")

copy this right and in E2 make a minor change to allow for 4+ not only 4:

=IF(--(SUBSTITUTE(SUBSTITUTE(VLOOKUP($A2,$K$1:$L$13,2,0)," week",""),"s",""))>=4,"Y","N")

 

[Kaper]

The bottom one is only for 4+ column.