HELP WITH INDEX FORMULA

[Fernando Gomez]

I have a table with 3 column:
Sales Rep Cust. # Territory
Ron 5001 5
Ron 5002 6
Paul 5003 10
paul 5004 11

I am trying to come up with a formula where in another sheet I have
Sales Rep Cust. # Territory
Ron 5001 Formula (I have been trying to use index, but
nothing worked for me)
Any help from somebody.

Thanks

 

[Dan E]

Fernando,

A few options:

Setup:
On Sheet 1 your table in A1:C5 (data in A2:C5)
Sales Rep Cust. # Territory
Ron 5001 5
Ron 5002 6
Paul 5003 10
Paul 5004 11

On Sheet 2 your lookup in A1:C1
Ron 5002 FORMULA

FORMULA
=LOOKUP(A1&B1,Sheet1!A2:A5&Sheet1!B2:B5,Sheet1!C2:C5)
OR
=SUMPRODUCT((Sheet1!A2:A5=A1)*(Sheet1!B2:B5=B1)*(Sheet1!C2:C5))
only works if Territory will always be a number
OR
=INDEX(Sheet1!C2:C5,MATCH(A1&B1,Sheet1!A2:A5&Sheet1!B2:B5,0))
Array Entered (Control + Shift + Enter)
If done right { } will appear around your formula ie:
{=INDEX(Sheet1!C2:C5,MATCH(A1&B1,Sheet1!A2:A5&Sheet1!B2:B5,0))}

Dan E

 

[Dan E]

NOTE:

The LOOKUP is rather unreliable, I would suggest one of the other
two options. The SUMPRODUCT is the best, (not array) but only
if territory will always be a number...

Dan E

 

[Dan E]

I also forgot that if you do use the LOOKUP remember
that your list must be sorted Ascending based on Name
then on Cust#

Dan E

I apologize for the repeated postings...

 

[Fernando Gomez]

Thanks Dan,

The Index formula is the only one that works perfect,

Thank you