Index Lookup Question
 

I am working on an index function to pull the first two letters out of a part
number and put them into pivot table and return a result, Here is what my
part numbers look like :

PA01000P
PA01100P

Here is what my formula looks like :

=INDEX(TaxGroup,MATCH(LEFT('Inventory Master'!P2,2)&"*",TaxGroup,0))

Tax Group is my pivot table which looks like this :

A B
PA P1COMPBR
PB P1COMPBR
PC P1PRESTO

My match function is working perfectly and returns the first to letters, but
im loosing it in the index function. When I run the function help menu it
tells me

MATCH(LEFT('Inventory Master'!P11,2)&"*",TaxGroup,0) = #N/A

I have my table sorted by ascending and it is in text format ???

Any help would sure be appreciated

Index Lookup Question
 

On Aug 29, 1:50 pm, Noncentz303
wrote:
I am working on an index function to pull the first two letters out of a part
number and put them into pivot table and return a result, Here is what my
part numbers look like :

PA01000P
PA01100P

Here is what my formula looks like :

=INDEX(TaxGroup,MATCH(LEFT('Inventory Master'!P2,2)&"*",TaxGroup,0))

Tax Group is my pivot table which looks like this :

A B
PA P1COMPBR
PB P1COMPBR
PC P1PRESTO

My match function is working perfectly and returns the first to letters, but
im loosing it in the index function. When I run the function help menu it
tells me

MATCH(LEFT('Inventory Master'!P11,2)&"*",TaxGroup,0) = #N/A

I have my table sorted by ascending and it is in text format ???

Any help would sure be appreciated

Why not try Vlookup
=Vllookup((LEFT('Inventory Master'!P11,2),taxgroup,2,false)

assuming taxgroup is the range name for following
A B
PA P1COMPBR
PB P1COMPBR
PC P1PRESTO

Index Lookup Question
 

The problem is that the lookup_array "TaxGroup" is a 2 dimensional array.
The lookup_array *must* be a 1 dimensional array (a single row or single
column).

Also, you haven't defined the column number in the INDEX function.

Try this:

=INDEX(TaxGroup,MATCH(LEFT('Inventory
Master'!P2,2)&"*",INDEX(TaxGroup,,1),0),2)


--
Biff
Microsoft Excel MVP


"Noncentz303" wrote in message
...
I am working on an index function to pull the first two letters out of a
part
number and put them into pivot table and return a result, Here is what my
part numbers look like :

PA01000P
PA01100P

Here is what my formula looks like :

=INDEX(TaxGroup,MATCH(LEFT('Inventory Master'!P2,2)&"*",TaxGroup,0))

Tax Group is my pivot table which looks like this :

A B
PA P1COMPBR
PB P1COMPBR
PC P1PRESTO

My match function is working perfectly and returns the first to letters,
but
im loosing it in the index function. When I run the function help menu it
tells me

MATCH(LEFT('Inventory Master'!P11,2)&"*",TaxGroup,0) = #N/A

I have my table sorted by ascending and it is in text format ???

Any help would sure be appreciated

Index Lookup Question
 

2 answers within 5 minutes with 2 DANG good results .... Thanks all

"T. Valko" wrote:

The problem is that the lookup_array "TaxGroup" is a 2 dimensional array.
The lookup_array *must* be a 1 dimensional array (a single row or single
column).

Also, you haven't defined the column number in the INDEX function.

Try this:

=INDEX(TaxGroup,MATCH(LEFT('Inventory
Master'!P2,2)&"*",INDEX(TaxGroup,,1),0),2)


--
Biff
Microsoft Excel MVP


"Noncentz303" wrote in message
...
I am working on an index function to pull the first two letters out of a
part
number and put them into pivot table and return a result, Here is what my
part numbers look like :

PA01000P
PA01100P

Here is what my formula looks like :

=INDEX(TaxGroup,MATCH(LEFT('Inventory Master'!P2,2)&"*",TaxGroup,0))

Tax Group is my pivot table which looks like this :

A B
PA P1COMPBR
PB P1COMPBR
PC P1PRESTO

My match function is working perfectly and returns the first to letters,
but
im loosing it in the index function. When I run the function help menu it
tells me

MATCH(LEFT('Inventory Master'!P11,2)&"*",TaxGroup,0) = #N/A

I have my table sorted by ascending and it is in text format ???

Any help would sure be appreciated