Selecting an unmatched value


M

Matthew

Help,
Its a ROSTA thing

Sheet 1
I have a list of 500 sites in col A
In cols B through N I have entries for people who could cover the site
in A.

Some may be 1 person some maybe 12 people.
Sheet2
What I am trying to do by any method is on a separte sheet to
automaticaly select from the list on sheet 1 a person to cover the
centres listed.

The bit I am stuck on is that the people listed on sheet may be able
to cover a number of sites. So the choice I would like to make would
only use a person not used already.

I think I would need to send an example so if anyone wants to help let
me know because at the moment all i have is this
=IF(ISERROR(MATCH(VLOOKUP($D100,who,3,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
3,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
4,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
5,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,6,FALSE),VLOOKUP($D100,who,
7,FALSE)))))))

Where:
D is the site number column
P is the column that I am comparing (obviously gets biger as we go
down)
Who is the range that I am checking against

As you can all see a complete mess, not my best moment.

Please help

Matthew
 
Ad

Advertisements

D

Don Guillett Excel MVP

Help,
Its a ROSTA thing

Sheet 1
I have a list of 500 sites in col A
In cols B through N I have entries for people who could cover the site
in A.

Some may be 1 person some maybe 12 people.
Sheet2
What I am trying to do by any method is on a separte sheet to
automaticaly select from the list on sheet 1 a person to cover the
centres listed.

The bit I am stuck on is that the people listed on sheet may be able
to cover a number of sites. So the choice I would like to make would
only use a person not used already.

I think I would need to send an example so if anyone wants to help let
me know because at the moment all i have is this
=IF(ISERROR(MATCH(VLOOKUP($D100,who,3,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
3,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
4,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
5,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,6,FALSE),VLOOKUP($D100,who,
7,FALSE)))))))

Where:
D is the site number column
P is the column that I am comparing (obviously gets biger as we go
down)
Who is the range that I am checking against

As you can all see a complete mess, not my best moment.

Please help

Matthew

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum Vlookup where there are blanks 1
#VALUE! 10
VLOOKUP and colour 1
vlookup formula 20
VLOOKUP Function 8
Automaticaly Enter Date in selected Cell 2
choose match formula 2
Case Sensitive v-lookup needed 3

Top