I have a sheet set up with names. I have 2 other sheets with spouses and
children that belong to the first sheet. I want to insert a formula that
will look up the correct spouse (based on a member number) and put in it the
column. Same with the children. I tried the vlookup function, but it did
not return the correct result. Any suggestions on the best formula? Thank
you.

The VLOOKUP() function is the one to use. How exactly are you trying to
use it?


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=521875

Here is how I have it set up:

lookup_value = column on main sheet with member numbers
table_array = corresponding list of member numbers on the spouse sheet
col_index_num = column on sposue sheet with the spouse's name
range_lookup = not being used




"Cutter" wrote:


The VLOOKUP() function is the one to use. How exactly are you trying to
use it?


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=521875

I was suggesting you provide your exact formula in order to know what
ranges you're using, but here's a generic example to work with:

=VLOOKUP(B3,Sheet2!$A$1:$E$35,3,0)

The B3 would be your member number (held in cell B3 on Sheet1).
The Sheet2! would be the name of your sheet holding the spouse info.
The $A$1:$E$35 would be the table on Sheet2 that holds the info.
Col A in this example would hold the member numbers.
The 3 tells Excel to get the info from the 3rd column (in this example
it would be Col C).
The 0 tells Excel to find an exact match for your search value.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=521875

Thank you for your help, that was perfect. My next problem is with the
dependents. The dependents are on another sheet. The problem is there are
more than one child per family. How can I pull the children to the master
sheet as well? I copied the spouse formula and it pulled the first child.
But I need to get the rest. Here is how the children are set up on the sheet:

101 Alex Smith
102 Jamie Dell
102 Christy Dell
etc.

"Cutter" wrote:


I was suggesting you provide your exact formula in order to know what
ranges you're using, but here's a generic example to work with:

=VLOOKUP(B3,Sheet2!$A$1:$E$35,3,0)

The B3 would be your member number (held in cell B3 on Sheet1).
The Sheet2! would be the name of your sheet holding the spouse info.
The $A$1:$E$35 would be the table on Sheet2 that holds the info.
Col A in this example would hold the member numbers.
The 3 tells Excel to get the info from the 3rd column (in this example
it would be Col C).
The 0 tells Excel to find an exact match for your search value.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=521875

It looks like you'll have to go to a complex formula using COUNTIF,
OFFSET and MATCH judging by the way you have your setup.

You could stick with VLOOKUP() by simply switching the additional
dependents to adjacent columns instead of adjacent rows.

How would you like to proceed?


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=521875