VLookup for multiple values!!
 

Hello friends my second thread, is related to the first one.

Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
has 8 columns, 1200 rows (all unique), and detail has 6 columns and
29000 rows (with duplicates). The records in sheet "header" A2:A1200
are unique. Data in each sheet starts in Row 2, with row 1 being the
headers for the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

The problem I am facing is
The second sheet column E I am looking up has different enteries for a
single value of A. How can I display all of those values for each
unique value of A of sheet "detail"?
e.g.
Sheet "Header" column A
169255
169226
169247
169248
Sheet "Detail" column A and E
--- A ------ E --
169255 U409A
169255 J237A
169226 J103A
169226 D303A
169226 6108B
169226 1102A
169248 1102A
169248 7862A

Thanks in advance to all of you who take out time to help out other
users.
:confused: Navneet :confused:


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388306

Reverse your logic. Use the detail sheet to look up the unique header record
with vlookup. Sort or use a filter to make your data readable.

Lance

"navneetjn" wrote:


Hello friends my second thread, is related to the first one.

Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
has 8 columns, 1200 rows (all unique), and detail has 6 columns and
29000 rows (with duplicates). The records in sheet "header" A2:A1200
are unique. Data in each sheet starts in Row 2, with row 1 being the
headers for the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

The problem I am facing is
The second sheet column E I am looking up has different enteries for a
single value of A. How can I display all of those values for each
unique value of A of sheet "detail"?
e.g.
Sheet "Header" column A
169255
169226
169247
169248
Sheet "Detail" column A and E
--- A ------ E --
169255 U409A
169255 J237A
169226 J103A
169226 D303A
169226 6108B
169226 1102A
169248 1102A
169248 7862A

Thanks in advance to all of you who take out time to help out other
users.
:confused: Navneet :confused:


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388306

Yeah that worked. I reversed the logic and got the unique numbers to
populate the detail sheet. Then I filtered the column on the N/A and
deleted the N/A data.
Voila!!! I have the list that I need.

One drawback

eg The result I am getting is in the following format (A big print
job)
-+---A----+---B-----+
1| 16196 | L1610 |
-+---------+---------+
2| 16196 | L2659 |
-+---------+---------+
3| 16196 | B254C |
-+---------+---------+
Instead of
-+----A---+----B----+---C---+---D----+
1| 16196 | L1610 | L2659 | B254C |
-+---------+---------+--------+---------+

Where ABCD are the column numbers and 123 are the row numbers. That is
ok as long as I get the result ;)

Thanks a lot.


LanceB Wrote:
Reverse your logic. Use the detail sheet to look up the unique header
record
with vlookup. Sort or use a filter to make your data readable.

Lance


--
navneetjn
------------------------------------------------------------------------
navneetjn's Profile: http://www.excelforum.com/member.php...o&userid=25330
View this thread: http://www.excelforum.com/showthread...hreadid=388306

On your sheet called 'Header', enter the following formula in B2, copy
across and down:

=IF(COLUMNS($B2:B2)<=COUNTIF(Detail!$A$2:$A$8,$A2) ,INDEX(Detail!$E$2:$E$8,SMALL(IF(Detail!$A$2:$A$8= $A2,ROW(Detail!$E$2:$E$8)-ROW(Detail!$E$2)+1),COLUMNS($B2:B2))),"")
...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

navneetjn Wrote:
Hello friends my second thread, is related to the first one.

Sheet1 is named "header" Sheet2 is named "detail". The sheet "header"
has 8 columns, 1200 rows (all unique), and detail has 6 columns and
29000 rows (with duplicates). The records in sheet "header" A2:A1200
are unique. Data in each sheet starts in Row 2, with row 1 being the
headers for the data.

I used the following formula in the "header" sheet at cell location I2
=VLOOKUP(A2,DETAIL!$A$2:$F$29497,5,FALSE)
and pulled this formula across 1200 records in sheet "header" to show
the data corresponding to A2 in the range $A$1:$F29497 for the 5th
column.

The problem I am facing is
The second sheet column E I am looking up has different enteries for a
single value of A. How can I display all of those values for each
unique value of A of sheet "detail"?
e.g.
Sheet "Header" column A
169255
169226
169247
169248
Sheet "Detail" column A and E
--- A ------ E --
169255 U409A
169255 J237A
169226 J103A
169226 D303A
169226 6108B
169226 1102A
169248 1102A
169248 7862A

Thanks in advance to all of you who take out time to help out other
users.
:confused: Navneet :confused:


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=388306