Nested MATCH with two tables

[Dave Lagergren]

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.

 

[Pete_UK]

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

 

[Dave Lagergren]

OK, I got it. Thanks for the help!

 

[Pete_UK]

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

Pete