Array - matching data
 

Hi
I have an array of columns "skills" and rows "names" with "level" as
intersecting data.

I can extract data by using index and match if I know 2 of the variables BUT
if I only know 1 variable i.e. skill what do I do... :(

To clarify, I have a skill and want to find and display the name and level
of those who have the skill.

Can anyone help please.... :)

Array - matching data
 

Is "level" a number?

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi
I have an array of columns "skills" and rows "names" with "level" as
intersecting data.

I can extract data by using index and match if I know 2 of the variables
BUT
if I only know 1 variable i.e. skill what do I do... :(

To clarify, I have a skill and want to find and display the name and level
of those who have the skill.

Can anyone help please.... :)

Array - matching data
 

Here's a small sample file:

delete2.xls 14kb

http://cjoint.com/?jdxRNVWILb

See if that's what you had in mind.

The formula to extract the names is an array formula**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Is "level" a number?

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi
I have an array of columns "skills" and rows "names" with "level" as
intersecting data.

I can extract data by using index and match if I know 2 of the variables
BUT
if I only know 1 variable i.e. skill what do I do... :(

To clarify, I have a skill and want to find and display the name and
level
of those who have the skill.

Can anyone help please.... :)

Array - matching data
 

Hi,

If I could get you a knighthood I would. You are brilliant is all I can say.

many thanks.
Colin

"T. Valko" wrote:

Here's a small sample file:

delete2.xls 14kb

http://cjoint.com/?jdxRNVWILb

See if that's what you had in mind.

The formula to extract the names is an array formula**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Is "level" a number?

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi
I have an array of columns "skills" and rows "names" with "level" as
intersecting data.

I can extract data by using index and match if I know 2 of the variables
BUT
if I only know 1 variable i.e. skill what do I do... :(

To clarify, I have a skill and want to find and display the name and
level
of those who have the skill.

Can anyone help please.... :)

Array - matching data
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi,

If I could get you a knighthood I would. You are brilliant is all I can
say.

many thanks.
Colin

"T. Valko" wrote:

Here's a small sample file:

delete2.xls 14kb

http://cjoint.com/?jdxRNVWILb

See if that's what you had in mind.

The formula to extract the names is an array formula**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Is "level" a number?

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi
I have an array of columns "skills" and rows "names" with "level" as
intersecting data.

I can extract data by using index and match if I know 2 of the
variables
BUT
if I only know 1 variable i.e. skill what do I do... :(

To clarify, I have a skill and want to find and display the name and
level
of those who have the skill.

Can anyone help please.... :)

Array - matching data
 

Biff,

Your going to hate me But I have another match problem. (I am trying to get
an intermediate excel course so thanks for patience and help)
I have created an example but cannot find how to attach it.
In essence I have a register that records course / attendee details by row.
The column headings are such as Register ID, name, dept, team, course, cost
etc.

My task is to create 3 searchs to display the data on a sheet where I have
designed a suitable format for each:
1) Show consultants and various details by Dept (Has a drop down list to
select Dept)
2) Show consultants and various details by team (Has a drop down list to
select team)
3) for a given name show certain details i.e. course, cost, course register
ID, status
p.s. there are 3 levels of status, Approved, Reserved or All (Has a drop
down list to select status and name).

I've tried to amend your formula including setting "named" ranges but to no
avail.

Thanks as I am on a very tight deadline.

I don't know what I can do for you but please ask :)

Colin


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi,

If I could get you a knighthood I would. You are brilliant is all I can
say.

many thanks.
Colin

"T. Valko" wrote:

Here's a small sample file:

delete2.xls 14kb

http://cjoint.com/?jdxRNVWILb

See if that's what you had in mind.

The formula to extract the names is an array formula**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Is "level" a number?

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi
I have an array of columns "skills" and rows "names" with "level" as
intersecting data.

I can extract data by using index and match if I know 2 of the
variables
BUT
if I only know 1 variable i.e. skill what do I do... :(

To clarify, I have a skill and want to find and display the name and
level
of those who have the skill.

Can anyone help please.... :)

Array - matching data
 

If the file is <1mb in size and you want to send a copy to me I'll take a
look. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

No guarantees!

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Biff,

Your going to hate me But I have another match problem. (I am trying to
get
an intermediate excel course so thanks for patience and help)
I have created an example but cannot find how to attach it.
In essence I have a register that records course / attendee details by
row.
The column headings are such as Register ID, name, dept, team, course,
cost
etc.

My task is to create 3 searchs to display the data on a sheet where I have
designed a suitable format for each:
1) Show consultants and various details by Dept (Has a drop down list to
select Dept)
2) Show consultants and various details by team (Has a drop down list to
select team)
3) for a given name show certain details i.e. course, cost, course
register
ID, status
p.s. there are 3 levels of status, Approved, Reserved or All (Has a drop
down list to select status and name).

I've tried to amend your formula including setting "named" ranges but to
no
avail.

Thanks as I am on a very tight deadline.

I don't know what I can do for you but please ask :)

Colin


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi,

If I could get you a knighthood I would. You are brilliant is all I can
say.

many thanks.
Colin

"T. Valko" wrote:

Here's a small sample file:

delete2.xls 14kb

http://cjoint.com/?jdxRNVWILb

See if that's what you had in mind.

The formula to extract the names is an array formula**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Is "level" a number?

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
Hi
I have an array of columns "skills" and rows "names" with "level"
as
intersecting data.

I can extract data by using index and match if I know 2 of the
variables
BUT
if I only know 1 variable i.e. skill what do I do... :(

To clarify, I have a skill and want to find and display the name
and
level
of those who have the skill.

Can anyone help please.... :)