vlookup formula not working with data on separate sheet

A

akee

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

akee

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
 
C

Cutter

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

akee

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

Cutter

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

akee

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

Cutter

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

Cutter

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

Cutter

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

Cutter

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 th
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 inf
to show
 
A

akee

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
 
A

akee

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

Cutter

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 Ro
2.

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

akee

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),"")
 
C

Cutter

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

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

The OFFSET cell should be the cell immediately above the very firs
name in the column containing the children's names. It can't be A17
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 appear
in a cell lower than B
 
Top