a Vlookup question

[Melody4572]

I have a spreadsheet we are using to tell us what to code different companies
with. Some of these copanies are used for more then one purpose.

Now to try and make life easier for the person entering the codes I have a
simply lookup formula. it looks at the name of the company and tells them
what to code it at. My question is, how do I get it to pull up all the codes
based on the company?

Should I simply make the row more complex or is there a way I can tell the
frmula if there is more then one company listed with this name to give the
next listing.

The formula I am using is

IF(ISNA(VLOOKUP($B$5,'companies and codes'!A2:A133,3,FALSE)),"Not
Found",VLOOKUP($B$5,'companies and codes'!A:C,3,FALSE))

and it works great for the first listing ony.
For example

Stanley Brushes utilities code sub code
Stanley Brushes Service code sub code
Stanley Brushes Supplies code sub code
Stanley Brushes contract code sub code

 

[Jim Skrydlak]

Try making the lookup range a$2:a$133; otherwise, it will turn into a3:a134
for the second row, a4:a135 for the third, etc.

Good luck!

 

[Melody4572]

thank Jim, but I guess I didn't explain that very well. Sorry about that.

What I have duplicate company names in the A2:A133 range, column B, C ect
has different informatin in it.
What I need is for my lookup to say "Oh! Look they have four cells with the
same company in it and they each have a differnt code for billing, and here
they are. Instead I am getting, "meh she can use this one" for all of it.

 

[Melody4572]

Ok rereading the formula that was posted, I see what you were talking about
now. The "real" formula is a:a and not what it looks like. Opps.

 

[Jim Skrydlak]

So you want to return all values from the third column if the value in the
first column matches?

Jim