Vlookup returning multiple values for one value, Excel 2010

[samp]

Morning All,
after much searching i found the closest problem to mine in this thread;
http://www.excelforum.com/excel-form...59#post3081459

however i cannot get this formula to work across different excel documents.
Scenario is i'm trying to build an automated spreadsheet to return the needed data from one reference. So i populate the master and then somebody else will use the auto sheet to put in a customer po and click and drag the row to auto populate the data.
I have a master list where customer po is the only constant, i will need to return serial number and other data. both are attached.
I am using office 2010, but others that edit the target sheet may use 2007.

I have tried and tried to edit fomula below with no success

Please Login or Register  to view this content.

many thanks in advance for any attempts to help me on this!!

[kvsrinivasamurthy]

PL see attached file.
Both the files should be in same folder.

[samp]

Thanks kvsrinivasamurthy,
can i just ask a couple questions for my understanding?
why did the po numbers have to be indexed? is this where i was going wrong?

[samp]

Also does your formula look up against customer 5?
i need to look up against cell E3, this is for information for specific orders, not all orders from same customer.
thanks

[kvsrinivasamurthy]

In the formula change $C$2 to $E$2.

[samp]

Cheers guys, one last question kvsrinivasamurthy, could you past the local formula as from C5 as code please?
think i'm getting confused with all the extended file names.
many thanks
sam

[kvsrinivasamurthy]

Use the following Array formulas

in C5

Please Login or Register  to view this content.

in D5

Please Login or Register  to view this content.

[samp]

Thank you very much mate, this is perfect.
on the donloaded on it was about 3 lines of formula due to /tempinternet/local etc..
thank you lot so much
perfect

[samp]

Please Login or Register  to view this content.

i could replicate this in the same named work books but now when i transfer it to the confidential sheet (sorry cant post) as the above it will not work.
and i am making it array.
I'm so close but so far.

[Duoae]

I don't know if it matters or not but just comparing the code you posted with kvsrinivasamurthy's he has set a range of $G$3:$G$15=$E$2 whereas you're referencing the identical cell to the one you're currently "in" with the forumla:$K:$K=$E$2.

Maybe that's the issue?

[samp]

HI Duaoae, $E$2 is the look up cell - Purposefully made it the same so i had as little work to do editing formula as possible.
cheers.

[kvsrinivasamurthy]

Pl change like this,

Please Login or Register  to view this content.

[samp]

ok, so

Please Login or Register  to view this content.

will work

Please Login or Register  to view this content.

will not work.

[Duoae]

Yeah, I wasn't talking about the cell $E$2, I was talking about how you had a range for $F but not $K (or $C for that matter):

i.e. $K:$K=$E$2,ROW($F$3:$F$100

Which is why it was changed by kvsrinivasamurthy to:
$K:$K=$E$2,ROW($F:$F

I don't know why the two different formulas you posted don't work the same way though but I'm going off code only as I can't make formulas work with the two files above.

[samp]

Thanks Duoae, i will try that one mate.
just wondering if this is a restricted formula, i.e. will it only work on ranges less than a certain number of rows?