Nested MATCH with two tables
 

I have a spreadsheet with 3 worksheets. Two of the worksheets (Jason and
Eric) contain a list of accounts assigned to salesmen. The third worksheet,
where the formula is, has a master list of all accounts. Not all of the
accounts show who the salesman assigned to it is. I need to check the value
in column A and see if it is on either salesmen's list. The formula below
finds those assigned to Jason but not Eric.

=IF(MATCH(A4867, Jason!$A$1:$A$200, 0)0,"Jason McRae",IF(MATCH(A4867,
Eric!$A$1:$A$149, 0)0,"Eric Passeau",0))

I assumed this would look at array on the Jason sheet and if it found a
match it would return a value greater than 0 hence the account would be
labeled Jason. If it was 0 it would evaluate the next IF and either find an
account under Eric or get set to 0.


--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications

Nested MATCH with two tables
 

From Excel Help on MATCH:

" ... If MATCH is unsuccessful in finding a match, it returns the #N/A
error value ... "

So, if it is not found in Jason's sheet it will return an error,
rather than evaluate the second IF. To overcome this you need to trap
the error - using ISNA along the lines of:

=IF(ISNA(MATCH( ..Jason.. )),"not in Jason's sheet","Jason McRae")

and "not in Jason's sheet" expands to:

IF(ISNA(MATCH( ..Eric.. )),"neither sheet","Eric Passeau")

You can change "neither sheet" to 0 if you wish.

Hope this helps.

Pete

On Sep 21, 10:20 pm, Dave Lagergren
wrote:
I have a spreadsheet with 3 worksheets. Two of the worksheets (Jason and
Eric) contain a list of accounts assigned to salesmen. The third worksheet,
where the formula is, has a master list of all accounts. Not all of the
accounts show who the salesman assigned to it is. I need to check the value
in column A and see if it is on either salesmen's list. The formula below
finds those assigned to Jason but not Eric.

=IF(MATCH(A4867, Jason!$A$1:$A$200, 0)0,"Jason McRae",IF(MATCH(A4867,
Eric!$A$1:$A$149, 0)0,"Eric Passeau",0))

I assumed this would look at array on the Jason sheet and if it found a
match it would return a value greater than 0 hence the account would be
labeled Jason. If it was 0 it would evaluate the next IF and either find an
account under Eric or get set to 0.

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications

Nested MATCH with two tables
 

OK, I got it. Thanks for the help!
--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications


"Pete_UK" wrote:

From Excel Help on MATCH:

" ... If MATCH is unsuccessful in finding a match, it returns the #N/A
error value ... "

So, if it is not found in Jason's sheet it will return an error,
rather than evaluate the second IF. To overcome this you need to trap
the error - using ISNA along the lines of:

=IF(ISNA(MATCH( ..Jason.. )),"not in Jason's sheet","Jason McRae")

and "not in Jason's sheet" expands to:

IF(ISNA(MATCH( ..Eric.. )),"neither sheet","Eric Passeau")

You can change "neither sheet" to 0 if you wish.

Hope this helps.

Pete

On Sep 21, 10:20 pm, Dave Lagergren
wrote:
I have a spreadsheet with 3 worksheets. Two of the worksheets (Jason and
Eric) contain a list of accounts assigned to salesmen. The third worksheet,
where the formula is, has a master list of all accounts. Not all of the
accounts show who the salesman assigned to it is. I need to check the value
in column A and see if it is on either salesmen's list. The formula below
finds those assigned to Jason but not Eric.

=IF(MATCH(A4867, Jason!$A$1:$A$200, 0)0,"Jason McRae",IF(MATCH(A4867,
Eric!$A$1:$A$149, 0)0,"Eric Passeau",0))

I assumed this would look at array on the Jason sheet and if it found a
match it would return a value greater than 0 hence the account would be
labeled Jason. If it was 0 it would evaluate the next IF and either find an
account under Eric or get set to 0.

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications

Nested MATCH with two tables
 

You're welcome, Dave - thanks for feeding back.

Pete

On Sep 22, 12:22 am, Dave Lagergren
wrote:
OK, I got it. Thanks for the help!
--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications



"Pete_UK" wrote:
From Excel Help on MATCH:

" ... If MATCH is unsuccessful in finding a match, it returns the #N/A
error value ... "

So, if it is not found in Jason's sheet it will return an error,
rather than evaluate the second IF. To overcome this you need to trap
the error - using ISNA along the lines of:

=IF(ISNA(MATCH( ..Jason.. )),"not in Jason's sheet","Jason McRae")

and "not in Jason's sheet" expands to:

IF(ISNA(MATCH( ..Eric.. )),"neither sheet","Eric Passeau")

You can change "neither sheet" to 0 if you wish.

Hope this helps.

Pete

On Sep 21, 10:20 pm, Dave Lagergren
wrote:
I have a spreadsheet with 3 worksheets. Two of the worksheets (Jason and
Eric) contain a list of accounts assigned to salesmen. The third worksheet,
where the formula is, has a master list of all accounts. Not all of the
accounts show who the salesman assigned to it is. I need to check the value
in column A and see if it is on either salesmen's list. The formula below
finds those assigned to Jason but not Eric.

=IF(MATCH(A4867, Jason!$A$1:$A$200, 0)0,"Jason McRae",IF(MATCH(A4867,
Eric!$A$1:$A$149, 0)0,"Eric Passeau",0))

I assumed this would look at array on the Jason sheet and if it found a
match it would return a value greater than 0 hence the account would be
labeled Jason. If it was 0 it would evaluate the next IF and either find an
account under Eric or get set to 0.

--
Dave Lagergren
Manager - Data Applications
Wireless Management, Inc
Specializing in cellular wireless applications- Hide quoted text -

- Show quoted text -