excel vlookup help

[kevmeister123]

hi all.

i need some help in excel as i cant figure out how to arrange my data correctly

i have attached the file to look at.

on the print sheet, when i put the customer number into cell E2, the details for that company display fine.

however, at the same time as displaying all the customer details (again based on cell E2), i need to display all the relevant "notes" from the 'notes' sheet that reference that customer (where column A = same as 'print sheet' E2).
For example on 'print sheet' C18 needs to reference 'notes' H2
BUT also C19 - 'notes' H3
AND also C20 - 'notes' H4

so in words: vlookup E2, return ALL rows that match 'notes' column A

As it's extremely difficult to put into words i'm guessing it will be even harder to get a formular working. if there is no such formula, is there a better way of storing the data that would allow me to have the same outcome?

Many thanks in advance, any questions (as i know i've probably not been 100% clear) please let me know.

Kev

 

[xlbo]

Vlookup will only ever return 1 matching value - the 1st it finds in a vertical list

If you store the notes on the same line but in different columns, you could use

=if(vlookup(E2,Data,3,false)="","",vlookup(E2,Data,3,false) & if(vlookup(E2,Data,4,false)="","",vlookup(E2,Data,4,false) & if(vlookup(E2,Data,5,false)="","",vlookup(E2,Data,5,false) & etc etc for as many notes as you are likely to have

For a more robust solution, I owuld suggest that you need a custom function in VBA that can be applied as a worksheet function - if you are interested in that approach, please post in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question