I have a table:

Title Prod1 Prod2 Prod3
Rep $1 $2 $3
SRep $4 $5 $6
Mgr $7 $8 $9

If SRep sells Prod3 I want to be able to cross reference and return $6.
The table for me to search is static. The information that will change is
the salesperson's title & what product. How can I traverse this matrix other
than VLookup?

Thanks Much!

Rod wrote:
I have a table:

Title Prod1 Prod2 Prod3
Rep $1 $2 $3
SRep $4 $5 $6
Mgr $7 $8 $9

If SRep sells Prod3 I want to be able to cross reference and return $6.
The table for me to search is static. The information that will change is
the salesperson's title & what product. How can I traverse this matrix other
than VLookup?

Thanks Much!

Hello,

=INDEX(B2:D4,MATCH("SRep",A2:A4,0),MATCH("Prod3",B 1:D1,0))

Substitute hard-coded "SRep" and "Prod3" values with references to
lookup cells if desired.

What's wrong with using VLOOKUP?

Assuming your table is in the range A1:D4 -

A10 = lookup title = SRep
B10 = lookup product = Prod3

=VLOOKUP(A10,A1:D4,MATCH(B10,A1:D1,0),0)

=INDEX(B2:D4,MATCH(A10,A2:A4,0),MATCH(B10,B1:D1,0) )

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
I have a table:

Title Prod1 Prod2 Prod3
Rep $1 $2 $3
SRep $4 $5 $6
Mgr $7 $8 $9

If SRep sells Prod3 I want to be able to cross reference and return $6.
The table for me to search is static. The information that will change
is
the salesperson's title & what product. How can I traverse this matrix
other
than VLookup?

Thanks Much!

Assuming your table is located in A1:D4 exactly as shown...

=SUMPRODUCT((A$2:A$4="Srep")*(B$1:D$1="Prod1")*B$2 :D$4)

where you can use a cell reference for the "Srep" and "Prod1" (you didn't
tell us the layout of your data that references this table). You will have
to preface the above ranges with the Sheet name the table is locate on if
your will be putting the above formula on a different worksheet. The above
formula can be copied down or across as long at the cell references you use
for "Srep" and "Prod1" use relative addressing.

--
Rick (MVP - Excel)


"Rod" wrote in message
...
I have a table:

Title Prod1 Prod2 Prod3
Rep $1 $2 $3
SRep $4 $5 $6
Mgr $7 $8 $9

If SRep sells Prod3 I want to be able to cross reference and return $6.
The table for me to search is static. The information that will change
is
the salesperson's title & what product. How can I traverse this matrix
other
than VLookup?

Thanks Much!

Hi,

You could use:

=SUMPRODUCT(--(A$2:A$4=J1),--(B$1:D$1=K1),B$2:D$4)

Where J1 contains the Title and K1 contains the Product. Copy the formula
down as far as necessary.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rod" wrote in message
...
I have a table:

Title Prod1 Prod2 Prod3
Rep $1 $2 $3
SRep $4 $5 $6
Mgr $7 $8 $9

If SRep sells Prod3 I want to be able to cross reference and return $6.
The table for me to search is static. The information that will change
is
the salesperson's title & what product. How can I traverse this matrix
other
than VLookup?

Thanks Much!