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

KEYWORD	GOOGLE DESKTOP RANK	GOOGLE MOBILE RANK
shoes	[if shoes lines up with "desktop", pull the corresponding google rank]	[if shoes lines up with "smartphone", pull the corresponding google rank
mens shoes	""	""

<tbody>
</tbody>

raw data tab

KEYWORD	DEVICE	GOOGLE RANK
shoes	desktop	5
shoes	smartphone	3
mens shoes	desktop	8
mens shoes	smartphone	9

<tbody>
</tbody>

 

[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

KEYWORD	GOOGLE DESKTOP RANK	GOOGLE MOBILE RANK
shoes	[if shoes lines up with "desktop", pull the corresponding google url]	[if shoes lines up with "smartphone", pull the corresponding google url]
mens shoes	""	""

<tbody>
</tbody>

raw data tab

KEYWORD	DEVICE	GOOGLE RANK
shoes	desktop	/shoes
shoes	smartphone	/shoes-all
mens shoes	desktop	/shoes-for-men
mens shoes	smartphone	/shoes-for-men-2

<tbody>
</tbody>

 

[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

KEYWORD	GOOGLE DESKTOP URL	GOOGLE MOBILE URL
shoes	[if shoes lines up with "desktop", pull the corresponding google url]	[if shoes lines up with "smartphone", pull the corresponding google url]
mens shoes	""	""

<tbody>
</tbody>

raw data tab

KEYWORD	DEVICE	GOOGLE URL
shoes	desktop	/shoes
shoes	smartphone	/shoes-all
mens shoes	desktop	/shoes-for-men
mens shoes	smartphone	/shoes-for-men-2

<tbody>
</tbody>

 

[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.