Vlookup with If statement Issue

[guernica52]

Hi Guys,

I have a keyword data dump that has 2 of every keyword (one has data for desktops, one has data for smartphones)

I would like to write a vlookup formula that will first check if the keyword is "desktop". If so, pull the corresponding rank (which would skip over the smartphone rows).

I actually wrote this formula a while ago (lost it now) and it just combined a simple vlookup and if statement. Any help with this formula below would be really appreciated.

client facing tab
[TABLE="width: 500"]
<tbody>[TR]
[TD]KEYWORD[/TD]
[TD]GOOGLE DESKTOP RANK[/TD]
[TD]GOOGLE MOBILE RANK[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD][if shoes lines up with "desktop", pull the corresponding google rank][/TD]
[TD][if shoes lines up with "smartphone", pull the corresponding google rank[/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]""[/TD]
[TD]""[/TD]
[/TR]
</tbody>[/TABLE]

raw data tab
[TABLE="width: 500"]
<tbody>[TR]
[TD]KEYWORD[/TD]
[TD]DEVICE[/TD]
[TD]GOOGLE RANK[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD]desktop[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD]smartphone[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]desktop[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]smartphone[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

How about a SUMPRODUCT formula:

Excel 2010
	A	B	C
1	KEYWORD	DEVICE	GOOGLE RANK
2	shoes	desktop	5
3	shoes	smartphone	3
4	mens shoes	desktop	8
5	mens shoes	smartphone	9
raw data

Excel 2010
	A	B	C
1	KEYWORD	GOOGLE DESKTOP RANK	GOOGLE MOBILE RANK
2	shoes	5	3
3	mens shoes	8	9
client facing

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(('raw data'!$A$2:$A$5=$A2)*('raw data'!$B$2:$B$5="desktop")*'raw data'!$C$2:$C$5)
C2	=SUMPRODUCT(('raw data'!$A$2:$A$5=$A2)*('raw data'!$B$2:$B$5="smartphone")*'raw data'!$C$2:$C$5)

Formulas copied down.

 

[guernica52]

Thank you!

I also need to pull in the ranking URL as well, so this formula wouldn't work for for that.

Is there another formula that would also work for this (see below)? Is the if statement + vlookup combo still an option?


client facing tab
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]KEYWORD[/TD]
[TD]GOOGLE DESKTOP RANK[/TD]
[TD]GOOGLE MOBILE RANK[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD][if shoes lines up with "desktop", pull the corresponding google url][/TD]
[TD][if shoes lines up with "smartphone", pull the corresponding google url][/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]""[/TD]
[TD]""[/TD]
[/TR]
</tbody>[/TABLE]


raw data tab
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]KEYWORD[/TD]
[TD]DEVICE[/TD]
[TD]GOOGLE RANK[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD]desktop[/TD]
[TD]/shoes[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD]smartphone[/TD]
[TD]/shoes-all[/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]desktop[/TD]
[TD]/shoes-for-men[/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]smartphone[/TD]
[TD]/shoes-for-men-2[/TD]
[/TR]
</tbody>[/TABLE]

 

[guernica52]

*headers fixed

Thank you!

I also need to pull in the ranking URL as well, so this formula wouldn't work for for that.

Is there another formula that would also work for this (see below)? Is the if statement + vlookup combo still an option?


client facing tab
[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]KEYWORD[/TD]
[TD]GOOGLE DESKTOP URL[/TD]
[TD]GOOGLE MOBILE URL[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD][if shoes lines up with "desktop", pull the corresponding google url][/TD]
[TD][if shoes lines up with "smartphone", pull the corresponding google url][/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]""[/TD]
[TD]""[/TD]
[/TR]
</tbody>[/TABLE]



raw data tab
[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]KEYWORD[/TD]
[TD]DEVICE[/TD]
[TD]GOOGLE URL[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD]desktop[/TD]
[TD]/shoes[/TD]
[/TR]
[TR]
[TD]shoes[/TD]
[TD]smartphone[/TD]
[TD]/shoes-all[/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]desktop[/TD]
[TD]/shoes-for-men[/TD]
[/TR]
[TR]
[TD]mens shoes[/TD]
[TD]smartphone[/TD]
[TD]/shoes-for-men-2[/TD]
[/TR]
</tbody>[/TABLE]

 

[guernica52]

bump for answer. thanks!

 

[jtakw]

This should work for your latest query:

Excel 2010
	E	F	G
1	KEYWORD	DEVICE	GOOGLE RANK
2	shoes	desktop	/shoes
3	shoes	smartphone	/shoes-all
4	mens shoes	desktop	/shoes-for-men
5	mens shoes	smartphone	/shoes-for-men-2
raw data

Excel 2010
	E	F	G
1	KEYWORD	GOOGLE DESKTOP RANK	GOOGLE MOBILE RANK
2	shoes	/shoes	/shoes-all
3	mens shoes	/shoes-for-men	/shoes-for-men-2
client facing

Cell Formulas
Range	Formula
F2	{=INDEX('raw data'!E$2:G$5,MATCH(1,('raw data'!E$2:E$5=E2)*('raw data'!F$2:F$5="desktop"),0),3)}
G2	{=INDEX('raw data'!E$2:G$5,MATCH(1,('raw data'!E$2:E$5=E2)*('raw data'!F$2:F$5="smartphone"),0),3)}
Press CTRL+SHIFT+ENTER to enter array formulas.

PLEASE NOTE: These are Array Formulas, HOLD DOWN Control and Shift, then press Enter.
F2 Formula copied down.
G2 Formula copied down.