transpose using vlookup index combo

**cube04** · 04-29-2009, 04:19 AM

i have data in column b,c,d and i want to fill h5,h6 and I5,I6 . please see the attached excel. tell me the formulas to put in h5 ,h6 etc. thanks in advance

**windknife** · 04-29-2009, 04:35 AM

Sumproduct function is ok! See attached.

HTH,
windknife

**cube04** · 04-29-2009, 04:39 AM

neat ! but can you tell through vlookup and index

**DonkeyOte** · 04-29-2009, 08:08 AM

Using LOOKUP:

H5: =LOOKUP(2,1/(($B$7:$B$10=$G5)*($C$7:$C$10=H$4)),$D$7:$D$10)
applied across matrix

You can't do this with VLOOKUP, and INDEX only really viable in non-array sense if you create a Concatenation key (at which point you could just use INDEX) or even SUMIF -- only a SUMIF/SUMPRODUCT will handle multiple instances of the combination of week & name should they exist.

But... use a Pivot Table...