Prev: Delete rows with Symbols
Next: Sumproduct;3 criteria
From: Nikki on 4 Jun 2010 11:15
I have a situation where I need to lookup Product A but then lookup Company A
in a column that changes.

vlookup("Product A",A1:T25,hlookup("Company A",A1:T25,0)) but can't seem to
get this to work.

The vlookup area has Product A listed 5 times but Company A (horizontally is
only listed 1 time). I hope this makes sense but I can't seem to get it to
work.

Example - my total for Product A and Company A should be $500

Company A Company B Company C
Product A 0 100 0
Product A 0 100 0
Product A 0 100 0
Product A 100 100 0
Product A 100 100 100

From: Jackpot on 4 Jun 2010 11:22
Try the below.Change the text strings to cell references...

=SUMIF(A:A,"ProductA",OFFSET(A:A,,MATCH("Company A",A1:J1,0)-1))

OR

=SUMIF(A:A,"ProductA",OFFSET(B:B,,MATCH("Company A",A1:J1,0)-2))


"Nikki" wrote:

> I have a situation where I need to lookup Product A but then lookup Company A
> in a column that changes.
>
> vlookup("Product A",A1:T25,hlookup("Company A",A1:T25,0)) but can't seem to
> get this to work.
>
> The vlookup area has Product A listed 5 times but Company A (horizontally is
> only listed 1 time). I hope this makes sense but I can't seem to get it to
> work.
>
> Example - my total for Product A and Company A should be $500
>
> Company A Company B Company C
> Product A 0 100 0
> Product A 0 100 0
> Product A 0 100 0
> Product A 100 100 0
> Product A 100 100 100
>
From: Nikki on 4 Jun 2010 11:40
I have separate tabs and the named range for my data to lookup is Company07.
I can get the Hlookup to work but it will not total the five lines for
Product A.

"Jackpot" wrote:

> Try the below.Change the text strings to cell references...
>
> =SUMIF(A:A,"ProductA",OFFSET(A:A,,MATCH("Company A",A1:J1,0)-1))
>
> OR
>
> =SUMIF(A:A,"ProductA",OFFSET(B:B,,MATCH("Company A",A1:J1,0)-2))
>
>
> "Nikki" wrote:
>
> > I have a situation where I need to lookup Product A but then lookup Company A
> > in a column that changes.
> >
> > vlookup("Product A",A1:T25,hlookup("Company A",A1:T25,0)) but can't seem to
> > get this to work.
> >
> > The vlookup area has Product A listed 5 times but Company A (horizontally is
> > only listed 1 time). I hope this makes sense but I can't seem to get it to
> > work.
> >
> > Example - my total for Product A and Company A should be $500
> >
> > Company A Company B Company C
> > Product A 0 100 0
> > Product A 0 100 0
> > Product A 0 100 0
> > Product A 100 100 0
> > Product A 100 100 100
> >
From: FloMM2 on 4 Jun 2010 11:43
Nikki,
Another solution:
In the cell for the total of "Product A" from "Company A" type this:
"=IF(B1="Company A",SUMIF(A2:A25,"Product A",C2:C25),0)" without the
beginning " and ending ".

This is with Column A filled with Product A, cell B1 has Company A in it.
Cells B2:B6 has 0, 0, 0, 100, 100. Cells C2:C6 has 100, 100, 100, 100, 100.
Cell D1 has Company B in it. Cells D2:D6 has 0, 0, 0, 0, 100. Cell C1 has
Comapny C in it. Cells C2:C6 are empty.

hth


"Jackpot" wrote:

> Try the below.Change the text strings to cell references...
>
> =SUMIF(A:A,"ProductA",OFFSET(A:A,,MATCH("Company A",A1:J1,0)-1))
>
> OR
>
> =SUMIF(A:A,"ProductA",OFFSET(B:B,,MATCH("Company A",A1:J1,0)-2))
>
>
> "Nikki" wrote:
>
> > I have a situation where I need to lookup Product A but then lookup Company A
> > in a column that changes.
> >
> > vlookup("Product A",A1:T25,hlookup("Company A",A1:T25,0)) but can't seem to
> > get this to work.
> >
> > The vlookup area has Product A listed 5 times but Company A (horizontally is
> > only listed 1 time). I hope this makes sense but I can't seem to get it to
> > work.
> >
> > Example - my total for Product A and Company A should be $500
> >
> > Company A Company B Company C
> > Product A 0 100 0
> > Product A 0 100 0
> > Product A 0 100 0
> > Product A 100 100 0
> > Product A 100 100 100
> >
 | 
Pages: 1
Prev: Delete rows with Symbols
Next: Sumproduct;3 criteria