Dividing multiple cells using Vlookup function

[agerrard]

Hi everone,

Im using excel 2003 SP2 on Windows XP. In the below example i currently have a calculation set up to determine relativity between 2 markets (see H13:I17) this is working fine as the order in the data dump (i.e. the order of the products) does not change.

But from now on the order of the products will change so i have to search using vlookup. I've been struggling with the vlookup however, as i cant get it to give me a number (it always returns #N/A).

So in H13 is want to write:

=if(iserror(vlookup(b13,d13:d17,1,false)/(vlookup(b13,c13:c17,1,false)/vlookup(b13,d4:d8,1,false)/(vlookup(b13,c4:c8,1,false)),0,vlookup(b13,d13:d17,1,false)/(vlookup(b13,c13:c17,1,false)/vlookup(b13,d4:d8,1,false)/(vlookup(b13,c4:c8,1,false))

but this isnt working...

Any ideas please?

mr excel_1.xls
	A	B	C	D	E	F	G	H	I
1
2			MATToSep06		QTRToSep07
3			Units(000)	Dollars(000)	Units(000)	Dollars(000)
4	MarketA	Product1	200.0	$1,200.0	50.0	$300.0
5		Product2	300.0	$1,450.0	75.0	$362.5
6		Product3	400.0	$1,600.0	100.0	$400.0
7		Product4	500.0	$1,890.0	125.0	$472.5
8		Product5	600.0	$2,500.0	150.0	$625.0
9
10
11			MATToSep06		QTRToSep07
12			Units(000)	Dollars(000)	Units(000)	Dollars(000)		MATRelativity	QTRRelativity
13	MarketB	Product1	450.0	$2,000.0	112.5	$500.0		74.1	74.1
14		Product2	550.0	$2,560.0	137.5	$640.0		96.3	96.3
15		Product3	650.0	$2,700.0	162.5	$675.0		103.8	103.8
16		Product4	750.0	$2,900.0	187.5	$725.0		102.3	102.3
17		Product5	900.0	$3,500.0	225.0	$875.0		93.3	93.3
Sheet1

 

[lionelnz]

So in H13 is want to write:

=if(iserror(vlookup(b13,d13:d17,1,false)/(vlookup(b13,c13:c17,1,false)/vlookup(b13,d4:d8,1,false)/(vlookup(b13,c4:c8,1,false)),0,vlookup(b13,d13:d17,1,false)/(vlookup(b13,c13:c17,1,false)/vlookup(b13,d4:d8,1,false)/(vlookup(b13,c4:c8,1,false))

but this isnt working...

Any ideas please?

The problem is with the tables in your lookup must include the lookup reference as well as in the defined table so try this

=if(iserror(vlookup($b13,$B$13:$D$17,3,false)/(vlookup($b13,$B$13:$D$17,2,false)/vlookup($b13,$B$4:$D$8,3,false)/(vlookup($b13,$B$4:$D$8,2,false)),0,vlookup(vlookup($b13,$B$13:$D$17,3,false)/(vlookup($b13,$B$13:$D$17,2,false)/vlookup($b13,$B$4:$D$8,3,false)/(vlookup($b13,$B$4:$D$8,2,false))

Hope this helps (also need absolute ref eg "$" to keep tables absolute when copying formulas down.

 

[jabba_jones]

I have split this up in two parts.

1) locate the row the produce is on in Market1

2) using that row, then calculate the MAT Relativity

In A13 Place:

=MATCH(B13,$B$1:$B$8,0)

and Fill A13:A17

In H13 Place:

=IF(ISERROR(A13),0,(D13/C13)/(INDIRECT("D"&A13)/INDIRECT("C"&A13)))*100

and Fill H13:H17

 

[Aladin Akyurek]

=(D13/C13)/(SUMIF($B$4:$B$8,$B13,$D$4:$D$8)/SUMIF($B$4:$B$8,$B13,$C$4:$C$8))*100