# Thread: VLOOKUP and IF Help

1. Registered User
Join Date
Nov 2007
Posts
2

## Unanswered: VLOOKUP and IF Help

Hi there,

I wonder if anyone can help me, I have a number of rows of data for the same person and am wondering how I can put the different rows all on one row, example below: -

WHAT I HAVE: -

Name - Child - Child's Name
John - Child 1 - Jack
John - Child 2 - Jill
John - Child 3 - Jane

WHAT I NEED: -
Name - Child 1 - Child 2 - Child 3
John - Jack - Jill - Jane

Any help would be greatly appreciated.

Thanks
Stuart

2. Registered User
Join Date
Oct 2003
Posts
1,091
Howdy, and welcome to the board.

Here is one way to do it. I added a fourth column (D on the attached), named it NameLU and put into cell D2 this formula:

=A2&B2

Then I used five named ranges:

NameList
=OFFSET(Sheet1!\$A\$2,0,0,COUNTA(Sheet1!\$A:\$A)-1,1)

ChildList
=OFFSET(NameList,0,1)

NameList
=OFFSET(NameList,0,2)

NameLU
=OFFSET(NameList,0,3)

NameArray
=OFFSET(Sheet1!\$A\$2,0,0,COUNTA(Sheet1!\$A:\$A)-1,3)

With the new names in Column F, and Child 1 in G, etc., I put John in F2, and in G2 this formula:

=INDEX(NameArray,MATCH(\$F2&G\$1,NameLU,FALSE),3)

Copy across and down as needed. This allows you to add as many names in columns A:C, and still work.

HTH

3. Registered User
Join Date
Nov 2007
Posts
2
Thanks very much for that, I didn't expect a response as fast as that!

It works perfectly, but do you know how the blank rows can automatically be deleted?

4. Registered User
Join Date
Oct 2003
Posts
1,091
Guess I am not sure what you mean by blank rows. If you are going to be adjusting the old data, then it would be better to put the lookup formulas on another worksheet. That way if you have blank rows in your original data you can delete without interferring with the lookups.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•