xl can help at comcast period net

Remove the word "can". Remove all the spaces. Replace the word "at" with the
at sign. Replace the word "period" with a dot.

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I am sorry I do not understand where to send this spreadsheet, can you
please
clarify?

Thanks Kerry

"T. Valko" wrote:

I think I'd need to see the data setup. If you want to send a copy of the
file I'm at:

xl can help at comcast period net

Remove "can" and change the obvious. If the file is big zip it!

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Yes I believe that this is an 'OR' condition and the value will NOT be
in
more than one location.

The original 'Parents' sheet I sent had column E = Identifier, F =
Parents
last name and G = parents first name using 3 columns for his
information.
In
actuality my sheet has 3 sets of this information side by side using up
9
columns. First group of 3 columns (E,F,G) pertains to parent #1, second
group
of 3 columns (I,J,K) pertains to parent #2 and the 3rd group of 3
columns
(M,N,O) pertains to parent #3. This format of columns allows me to deal
with
children who have divorced parents.

Your formula allowed me to search for a parent in the first group of 3
columns and attach the Childs address from Column 'D' on the 'Master'
sheet I
need the formula to search all three groups of 3 columns in order to
obtain
the answer and if there is no answer then respond with "----".

I hope that better explains my prediciment if not then the only way I
can
think to explain it is to send you my spreadsheet and I have no idea
how
to
do that.

Thanks,
Kerry

"T. Valko" wrote:

You'll have to refresh my memory! I'm gettin old!

I'm a bit confused over this:

search three separate columns (i.e 'E', 'I' and 'M')

I need to match data that may be in columns F&G or J&K or N&O

So, which columns do you need to search?

Is this an "or" condition? For example, the lookup value might be in
any
one
of 3 columns. It might be in column E or it might be in column I or it
might
be in column M. And, the lookup value will not be in more than 1 of
those
columns

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Well... I am now in way over my head. :(

The example I provided and that you solved, very nicely I should
add,
requires that the formula searches one column for the identifier, I
don't
suppose that you could tell me how I can adapt your formula to
search
three
separate columns (i.e 'E', 'I' and 'M') and return with a '----' if
it
cannot
find a solution? You matched the data in columns 'F' & 'G' but I
need
to
match data that may be in columns F&G or J&K or N&O

The problem is that I am not just searching in one column for the
identifier, but I am searching for the identifier in three separate
columns
('E', 'I' and 'M') and if an answer cannot be found the formula
needs
to
returns with '----'.

Sorry to snowball this dilemma but I really do appreciate your help.

Oh and by the way I am running Excel 2003 so thanks for the tip
about
Array's and whole columns. I guess I will have to stick with
specific
ranges
until I upgrade the program.

Thanks again,
Ksean



"T. Valko" wrote:

Is there anything I should watch for when I
start attempting to adapt this formula?

Well, like with *any* formula the bigger the range references the
longer
it
takes for the formula to calculate.

Also, since this is an array formula you can't use entire columns
as
range
references *unless* you're using Excel 2007. For example, you can't
do
this
*unless* you're using Excel 2007

=INDEX(Master!D:D,MATCH(1,(Master!F:F=B2)*(Master! G:G=C2),0))


--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
WOW... this formula is absolutly AWESOME!!

I would never of thought in a million years to hit
'CTRL,SHIFT,ENTER'
to
activate the formula!

It works really well on my small sample I just need to adapt it
to
my
real
working spreadsheets which ares substantially larger. Is there
anything
I
should watch for when I start attempting to adapt this formula?

Thanks
ksean

"T. Valko" wrote:

Enter this array formula** on the Parents sheet in cell D2:

=INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0))

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key
and
the
SHIFT
key then hit ENTER.

Copy down as needed.

Note that in your sample data there are duplicate parent names
with
different addresses:

4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave
5 3 Ansell Sean 3937-51 Street 35 Ansell Dave

The above formula will *only* find the address for the first
instance
of
the
parent names.

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Please let me rework my question and correct my table with the
following
example

I have three sheets of data, the first sheet is named "MASTER"
the
second
sheet is named "KIDS" and the third sheet is named "PARENTS"

On the MASTER sheet I have a database of kid's names complete
with
addresses
and parent's names
MASTER sheet example
A B C D E F G
1 No. Last First Address No. Last First
2 1 Ambeault Chelsea RR2 3 Ambeault Kyla
3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley
4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave
5 3 Ansell Sean 3937-51 Street 35 Ansell Dave
6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal
7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail
8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien
9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna

On the KIDS sheet I have a database that consists of the kid's
names
and
addresses. The formula that I am using is
=VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very
well.
KIDS sheet example
A B C D
1 No. Last First Address
2 1 Ambeault Chelsea RR2
3 3 Ansell Sean 3937-51 Street
4 6 Anderson Cache 419 Riverpark Dr
5 9 Arbuckle Jay 10 Westwood Wynd
6 11 Ansell Jaime 9609-100 Street
7 17 Banh Shally 10 Westwood Wynd
8 21 Barbour Michael 438 Riverpark Dr
9 25 Antoniuk Braden 8146-94 Avenue

On the PARENTS sheet I have a database that consists of the
parent's
names
and addresses. The formula that I am using
=VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very
well
for
the
names
but I cannot figure out how to reference the addresses.
PARENTS sheet example
A B C D
1 No. Last First Address
2 3 Ambeault Kyla ?
3 5 Anderson Shelley ?
4 7 Banh Tien ?
5 18 Ansell Dave ?
6 35 Ansell Dave ?
7 87 Barbour Dianna ?
8 159 Arbuckle Gail ?
9 215 Antoniuk Chantal ?

How do I get the address from the MASTER sheet to appear in
the
correct
place on the PARENTS sheet while only referencing the PARENTS
No.'s
from
column 'E' on the MASTER sheet?

Thanks
ksean

PS I don't know how to fix the MASTER table so it displays
better
in
this
forum...sorry!



"Mike H" wrote:

Hi,

I don't understand the question, in Vlookup the column that 3
refers
to
is
'always' the third column and the first column 'A' is always
the
one
being
looked up. Perhaps you would be better giving us the lookup
value
and
what
you expect to be returned from that but it sounds like you
may
mean
INDEX
&
MATCH.

Mike

"ksean" wrote:

When considering this formula
=VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE)
the
,3, refers to the second column to the right of a reference
column
on
the
Master sheet.

Using this formula and the following table where column 'C'
is
the
reference
column and the ,3, refers to column 'E'; is there a way to
refer
to
a
column