vlookup formula not working with data on separate sheet

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?

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.

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?

I have lots of children. If it is not too much trouble, could you walk me
through the more complex formula? Let me know if you need more information.

"Cutter" wrote:

>
> 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
>
>

OK, here's a generic sample:

With your lookup value in B3 of Sheet 1
Using the children's names in a table in range A2:F30 on Sheet3
(with the member numbers in Column A)
In the cell where you want the first child's name to appear, type:
=IF(COUNTIF(Sheet3!$A$2:$A$30,$B$3)>0,VLOOKUP($B$3,Sheet3!$A$2:$F$30,2,0),"")

for the cell where you want the second child's name, type:
=IF(COUNTIF(Sheet3!$A$2:$A$30,$B$3)>1,OFFSET(Sheet3!B1,MATCH($B$3,Sheet3!$A$2:$A$30,0)+1,0),"")

for the cell where you want the third child's name, type:
=IF(COUNTIF(Sheet3!$A$2:$A$30,$B$3)>2,OFFSET(Sheet3!$B$1,MATCH($B$3,Sheet3!$A$2:$A$30,0)+2,0),"")

for subsequent names of children you have to adjust 2 numbers in the formula: (the numbers appear after the > sign and the + sign)

for the 4th child change the 2's to 3's
for the fifth child the numbers would be 4's
and so on

The formulas will give "" (blank) results if there is no dependent info to show.

Here is my formula for the first child:

=IF(COUNTIF(children!$A$2:$A$1545,$A$5)>0,VLOOKUP($A$5,children!$A$2:$E$1545,2,0)," ")

children!$A$2:$A$1545 = is the sheet/column with the member numbers
$A$5 = the member number on the main sheet
children!$A$2:$E$1545 = the data range for all the children info
the children names would be in column 2 on the sheet

"Cutter" wrote:

>
> OK, here's a generic sample:
>
> With your lookup value in B3 of Sheet 1
> Using the children's names in a table in range A2:F30 on Sheet3
> (with the member numbers in Col A, names in Col B)
> In the cell where you want the first child's name to appear, type:
> =IF(COUNTIF(Sheet3!$A$2:$A$30,$B$3)>0,VLOOKUP($B$3,Sheet3!$A$2:$F$30,2,0),"")
>
> for the cell where you want the second child's name, type:
> =IF(COUNTIF(Sheet3!$A$2:$A$30,$B$3)>1,OFFSET(Sheet3!B1,MATCH($B$3,Sheet3!$A$2:$A$30,0)+1,0),"")
>
> for the cell where you want the third child's name, type:
> =IF(COUNTIF(Sheet3!$A$2:$A$30,$B$3)>2,OFFSET(Sheet3!$B$1,MATCH($B$3,Sheet3!$A$2:$A$30,0)+2,0),"")
>
> for subsequent names of children you have to adjust 2 numbers in the
> formula: (the numbers appear after the > sign and the + sign)
>
> for the 4th child change the 2's to 3's
> for the fifth child the numbers would be 4's
> and so on
>
> The formulas will give "" (blank) results if there is no dependent info
> to show.
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=521875
>
>

Are you asking for further assistance or have you got it working now?

I have it working for the first child. I am working on the other children
now. However, I am not sure what the B1 is referring to in the offset
formula.

"Cutter" wrote:

>
> Are you asking for further assistance or have you got it working now?
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=521875
>
>

That refers to the first cell above the first name in Col B.

I assumed you have a header row in row 1 and your names start in Row 2.

If your names start in Row 3 then change that reference to B2

I have the formula you gave me. However, the result is giving me #N/A.

=IF(COUNTIF(children!$A$2:$E$1545,'member
list'!B180)>1,OFFSET(children!A179,MATCH('member
list'!B180,children!$A$2:$E$1545,0)+1,0),"")

"Cutter" wrote:

>
> That refers to the first cell above the first name in Col B.
>
> I assumed you have a header row in row 1 and your names start in Row
> 2.
>
> If your names start in Row 3 then change that reference to B2
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=521875
>
>

You haven't adjusted the ranges properly. Have a look at the formula I provided for the 2nd child's name and follow it more carefully when changing the ranges to match your setup.

The MATCH portion and the COUNTIF portion should only be the column containing the member numbers but you have the entire table.

The OFFSET cell should be the cell immediately above the very first name in the column containing the children's names. It can't be A179 because the member numbers are in Col A

Try:

=IF(COUNTIF(children!$A$2:$A$1545,'memberlist'!B180)>1,OFFSET(children!B1,MATCH('memberlist'!B180,children!$A$2:$A$1545,0)+1,0),"")

But the children!B1 MAY need adjusting if your very first name appears in a cell lower than B2

Thank you for all your help. I have it working perfectly.

"Cutter" wrote:

>
> You haven't adjusted the ranges properly. Have a look at the formula I
> provided for the 2nd child's name and follow it more carefully when
> changing the ranges to match your setup.
>
> The MATCH portion and the COUNTIF portion should only be the column
> containing the member numbers but you have the entire table.
>
> The OFFSET cell should be the cell immediately above the very first
> name in the column containing the children's names. It can't be A179
> because the member numbers are in Col A
>
> Try:
>
> =IF(COUNTIF(children!$A$2:$A$1545,'memberlist'!B180)>1,OFFSET(children!B1,MATCH('memberlist'!B180,children!$A$2:$A$1545,0)+1,0),"")
>
> But the children!B1 MAY need adjusting if your very first name appears
> in a cell lower than B2
>
>
> --
> Cutter
> ------------------------------------------------------------------------
> Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
> View this thread: http://www.excelforum.com/showthread...hreadid=521875
>
>

You're welcome. Glad you got it working and thanks for the feedback.