Complex Lookup Functions

[bwhizz]

Hoping for some help regarding a complex Vlookup Funcion ....

here is a piece of the data table

Order Number Unit Ordered
570 Apple
570 Peach
570 Pear
571 Peach
571 Plum
572 Pear


Basically, what I want to do is type in 570 in one cell and then have Apple appear in Row 1, Peach appear in row 2 and Pear appear in Row 3 of a new worksheet....

How can this be done?

[NBVC]

See attached:

Formula is in Sheet 2 B2, copied down. Adjust ranges to suit your data in Sheet1

Note the formula is an array formula and must be confirmed with CTRL+SHIFT+ENTER after any changes are made. Then you can copy it down.

[Ron Coderre]

Perhaps you'd consider using a Pivot Table?

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

PAGE: Drag the Order_Number field here
ROW: Drag the Unit_Ordered field here
COLUMN: (leave this area blank)
DATA: Drag the Unit_Ordered field here...(It will display as Count of Unit_Ordered)
Click [OK]
Select where you want the Pivot Table…Click [Finish].

That will automatically list each Unit_Ordered and the count of each Unit_Ordered for whatever Order_Number you select in the PAGE field. Initially the count of Unit_Ordered for all orders will display. (If you like, you can just hide the Count of Unit_Ordered column.)

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?