Table Lookup

[Rod]

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!

 

[smartin]

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(B24,MATCH("SRep",A2:A4,0),MATCH("Prod3",B11,0))

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

 

[T. Valko]

What's wrong with using VLOOKUP?

Assuming your table is in the range A14 -

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

=VLOOKUP(A10,A14,MATCH(B10,A11,0),0)

=INDEX(B24,MATCH(A10,A2:A4,0),MATCH(B10,B11,0))

 

[Rick Rothstein]

Assuming your table is located in A14 exactly as shown...

=SUMPRODUCT((A$2:A$4="Srep")*(B$1$1="Prod1")*B$2$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.

 

[Shane Devenshire]

Hi,

You could use:

=SUMPRODUCT(--(A$2:A$4=J1),--(B$1$1=K1),B$2$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